Setting row results into columns in SQL Server

Question

I need to place some of my results in columns. See below example...

SELECT *
FROM tblAccount
WHERE Code IN ('H20', 'T10')
ORDER BY ID, YearPaid

Results...

ID   YearPaid     Amount   Code   Name
-------------------------------------------
1    2011-01-01    31 50   H20    Service
1    2011-01-01    45 00   T10    Service
2    2011-01-02    31 50   H20    Benefit
2    2011-01-02    45 00   T10    Benefit
3    2011-01-03    31 20   H20    Pay
3    2011-01-03    45 00   T10    Pay

I need the results to be displayed like below...

ID   YearPaid    Amount   Code   Name      Amount   Code
--------------------------------------------------------
1    2011-01-01   31 50   H20    Service    45 00   T10
2    2011-01-02   31 50   H20    Benefit    45 00   T10
3    2011-01-03   31 20   H20    Pay        45 00   T10

I thought of using a pivot, but I am not aggregating anything.

I could do temp tables for each code, but their has to be a better way.

Any help would be great!

You can still use a pivot. If you do a sum of a value column but group by the same amount of fields as in the table then your sum essentially just shows your normal data. Same idea here. — user2858650
– user2858650, Commented Oct 7, 2014 at 16:40

Lamak · Accepted Answer · 2014-10-07 16:40:16Z

2

I thought of using a pivot, but I am not aggregating anything, this is not necessarily true, many times when a user says that, you can see that you could use min or max. So, assuming that you can have 2 rows for each ID, YearPaid:

;WITH CTE AS
(
    SELECT  *,
            RN = ROW_NUMBER() OVER(PARTITION BY ID, YearPaid ORDER BY ID) 
    FROM tblAccount
    WHERE Code IN ('H20', 'T10')
)
SELECT  ID, 
        YearPaid,
        MIN(CASE WHEN RN = 1 THEN Amount END) Amount1,
        MIN(CASE WHEN RN = 1 THEN Code END) Code1,
        MIN(CASE WHEN RN = 1 THEN Name END) Name1,
        MIN(CASE WHEN RN = 2 THEN Amount END) Amount2,
        MIN(CASE WHEN RN = 2 THEN Code END) Code2,
        MIN(CASE WHEN RN = 2 THEN Name END) Name2
FROM CTE
GROUP BY ID, 
         YearPaid

answered Oct 7, 2014 at 16:40

Lamak

70.8k12 gold badges119 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Setting row results into columns in SQL Server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related