how to convert row to column in SQL

Can somebody help me with this SQL Query.

In the following table, RESPONSES counts how many times SEGMENT has res开发者_如何学JAVAponded on CHECKED date.

CREATE TABLE #TEST (ID INT, SEGMENT CHAR(1),RESPONSES
INT,CHECKED SMALLDATETIME)
INSERT INTO #TEST VALUES (1,'A',0,'2009-05-01')
INSERT INTO #TEST VALUES (2,'B',1,'2009-05-01')
INSERT INTO #TEST VALUES (3,'C',0,'2009-05-01')
INSERT INTO #TEST VALUES (4,'A',0,'2009-05-02')
INSERT INTO #TEST VALUES (5,'B',2,'2009-05-02')
INSERT INTO #TEST VALUES (6,'C',1,'2009-05-02')
INSERT INTO #TEST VALUES (7,'A',1,'2009-05-03')
INSERT INTO #TEST VALUES (8,'B',0,'2009-05-03')
INSERT INTO #TEST VALUES (9,'C',2,'2009-05-03')

Write a query to summarise total RESPONSES for each SEGMENT and each CHECKED date, shown in following format:

CHECKED      A      B       C
2009-5-01    0      1       0
2009-5-02    0      2       1
2009-5-03    1      0       2

Do NOT hard-code segment names (i.e. “A”, “B”, “C”) into your solution, so the solution remains functional if more segments are added (e.g. “D”) or segments are renamed (e.g. “A” -> “X”).

Select Checked
    , Sum( Case When Segment = 'A' Then 1 Else 0 End ) As A
    , Sum( Case When Segment = 'B' Then 1 Else 0 End ) As B
    , Sum( Case When Segment = 'C' Then 1 Else 0 End ) As C
From #Test
Group By Checked

This type of query is often called a crosstab query. The above solution assumes you want to statically declare which columns you want to see. If you want to dynamically determine the columns, then what you seek is a dynamic crosstab and it cannot be done natively in the SQL language. The SQL language was not designed for dynamic column generation. The solution is to build the query in your middle-tier.

You need to use dynamic SQL. See this blog post for an example. Another example, different blog, same approach.

If your columns are static and you're using SQL Server 2005 and higher you can use the PIVOT feature to perform this type of query.

Please see this on StackOverFlow: If using SQL Server 2005 or greater...

DECLARE  @test TABLE
(
    ID INT, 
    SEGMENT CHAR(1),
    RESPONSES INT,
    CHECKED SMALLDATETIME
)

INSERT INTO @test VALUES (1,'A',0,'2009-05-01')
INSERT INTO @test VALUES (2,'B',1,'2009-05-01')
INSERT INTO @test VALUES (3,'C',0,'2009-05-01')
INSERT INTO @test VALUES (4,'A',0,'2009-05-02')
INSERT INTO @test VALUES (5,'B',2,'2009-05-02')
INSERT INTO @test VALUES (6,'C',1,'2009-05-02')
INSERT INTO @test VALUES (7,'A',1,'2009-05-03')
INSERT INTO @test VALUES (8,'B',0,'2009-05-03')
INSERT INTO @test VALUES (9,'C',2,'2009-05-03')


SELECT * FROM 
(    
    SELECT  SEGMENT,
            RESPONSES,
            CHECKED
    FROM @test
) AS subquery 
PIVOT 
(
    SUM(responses) 
    FOR SEGMENT IN ([a],[b],[c])
) AS pivotquery

Dynamic SQL Example

CREATE TABLE ##test
(
    ID INT, 
    SEGMENT CHAR(1),
    RESPONSES INT,
    CHECKED SMALLDATETIME
)

INSERT INTO ##test VALUES (1,'A',0,'2009-05-01')
INSERT INTO ##test VALUES (2,'B',1,'2009-05-01')
INSERT INTO ##test VALUES (3,'C',0,'2009-05-01')
INSERT INTO ##test VALUES (4,'A',0,'2009-05-02')
INSERT INTO ##test VALUES (5,'B',2,'2009-05-02')
INSERT INTO ##test VALUES (6,'C',1,'2009-05-02')
INSERT INTO ##test VALUES (7,'A',1,'2009-05-03')
INSERT INTO ##test VALUES (8,'B',0,'2009-05-03')
INSERT INTO ##test VALUES (9,'C',2,'2009-05-03')

DECLARE @SQLa VARCHAR(255),
        @SQLb VARCHAR(255),
        @SQLc VARCHAR(255)

SET @SQLa =
'SELECT * FROM 
(    
    SELECT  SEGMENT,
            RESPONSES,
            CHECKED
    FROM ##test
) AS subquery 
PIVOT 
(
    SUM(responses) 
    FOR SEGMENT IN ('

SET @SQLc = ')
) AS pivotquery'

SELECT @sqlB = STUFF(
(
    SELECT ',[' + SEGMENT + ']'
    FROM ##test WITH (NOLOCK)
    GROUP BY SEGMENT
    FOR XML PATH('')
),1, 1, '')

EXECUTE (@SQLa + @SQLb + @SQLc)

DROP TABLE ##test