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 responded 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”).

回答1:

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

回答2:

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.

回答3:

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.