CODEWITHSUNDEEP
sqlsql-server
RMM
RMM

Reputation: 5

Setting row results into columns in SQL Server

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!

Upvotes: 0

Views: 44

Answers (1)

Lamak
Lamak

Reputation: 70638

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

Upvotes: 2

Related Questions