SQL 2005 Query

devendar · August 7th, 2008, 08:31 PM

Hi,

I need a Query for following table.

CREATE TABLE Test_Imp
(ID int,
Type1 int,
n1 char)

INSERT Test_Imp values(1,1,'a')
INSERT Test_Imp values(1,2,'b')
INSERT Test_Imp values(2,2,'c')
INSERT Test_Imp values(3,1,'d')

I need Output like

ID N1 N2
1 a b
2 Null c
3 d Null

Thanks in advance!

Peso · August 10th, 2008, 05:14 AM

Code:

DECLARE    @Sample TABLE (ID INT, Type1 INT, n1 CHAR)

INSERT    @Sample
SELECT    1, 1, 'a' UNION ALL
SELECT    1, 2, 'b' UNION ALL
SELECT    2, 2, 'c' UNION ALL
SELECT    3, 1, 'd'

-- SQL 2000
SELECT        ID,
        MAX(CASE WHEN Type1 = 1 THEN n1 ELSE NULL END) AS N1,
        MAX(CASE WHEN Type1 = 2 THEN n1 ELSE NULL END) AS N2
FROM        @Sample
GROUP BY    ID
ORDER BY    ID

-- SQL 2005
SELECT    p.ID,
    p.[1] AS N1,
    p.[2] AS N2
FROM    @Sample AS s
PIVOT    (
        MAX(n1)
        FOR Type1 IN ([1], [2])
    ) AS p