grouping and switching the columns and rows

问题:

I don't know if this would officially be called a pivot, but the result that I would like is this:

+------+---------+------+
| Alex | Charley | Liza |
+------+---------+------+
|  213 |     345 |    1 |
|   23 |     111 |    5 |
|   42 |      52 |    2 |
|  323 |         |    5 |
|   23 |         |    1 |
|  324 |         |    5 |
+------+---------+------+

my input data is in this form:

+-----+---------+
| Apt |  Name   |
+-----+---------+
| 213 | Alex    |
|  23 | Alex    |
|  42 | Alex    |
| 323 | Alex    |
|  23 | Alex    |
| 324 | Alex    |
| 345 | Charley |
| 111 | Charley |
|  52 | Charley |
|   1 | Liza    |
|   5 | Liza    |
|   2 | Liza    |
|   5 | Liza    |
|   1 | Liza    |
|   5 | Liza    |
+-----+---------+

because I have approximately 100 names, I don't want to have to do a ton of sub queries lik this

select null, null, thirdcolumn from...
select null, seconcolumn from...
select firstcolumn from...

Is there a way to do this with PIVOT or otherwise?

回答1:

You can do this with dynamic PIVOT and the ROW_NUMBER() function:

DECLARE @cols AS VARCHAR(1000),
        @query  AS VARCHAR(8000)
SELECT @cols = STUFF((SELECT ',' +   QUOTENAME(Name) 
                    FROM (SELECT DISTINCT Name
                          FROM #test
                          )sub
                    ORDER BY Name
                    FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)') 
                    ,1,1,'')
PRINT @cols

SET @query = '
WITH cte AS (SELECT DISTINCT *
             FROM  #test)
    ,cte2 AS (SELECT *,ROW_NUMBER() OVER(PARTITION BY Name ORDER BY Apt)RowRank
             FROM  cte)
SELECT * 
FROM  cte2 
PIVOT (max(Apt) for Name in ('+@cols+')) p
            '
EXEC (@query)

SQL Fiddle - Distinct List, Specific Order

Edit: If you don't want the list to be distinct, eliminate the first cte above, and if you want to keep arbitrary ordering change the ORDER BY to (SELECT 1):

DECLARE @cols AS VARCHAR(1000),
        @query  AS VARCHAR(8000)
SELECT @cols = STUFF((SELECT ',' +   QUOTENAME(Name) 
                    FROM (SELECT DISTINCT Name
                          FROM #test
                          )sub
                          ORDER BY Name
                    FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)') 
                    ,1,1,'')
PRINT @cols

SET @query = '
WITH cte AS (SELECT *,ROW_NUMBER() OVER(PARTITION BY Name ORDER BY (SELECT 1))RowRank
             FROM  #test)
SELECT * 
FROM  cte 
PIVOT (max(Apt) for Name in ('+@cols+')) p
            '
EXEC (@query)

SQL Fiddle - Full List, Arbitrary Order

And finally, if you didn't want the RowRank field in your results, just re-use the @cols variable in your SELECT:

SET @query = '
WITH cte AS (SELECT *,ROW_NUMBER() OVER(PARTITION BY Name ORDER BY (SELECT 1))RowRank
             FROM  #test)
SELECT '+@cols+' 
FROM  cte 
PIVOT (max(Apt) for Name in ('+@cols+')) p
            '
EXEC (@query)

回答2:

Oh, this is something of a pain, but you can do it with SQL. You are trying to concatenate the columns.

select seqnum,
       max(case when name = 'Alex' then apt end) as Alex,
       max(case when name = 'Charley' then apt end) as Charley,
       max(case when name = 'Liza' then apt end) as Liza
from (select t.*, row_number() over (partition by name order by (select NULL)) as seqnum
      from t
     ) t
group by seqnum
order by seqnum;

As a note: there is no guarantee that the original ordering will be the same within each column. As you know, SQL tables are inherently unordered, so you would need a column to specify the ordering.

To handle multiple names, I'd just get the list using a query such as:

select distinct 'max(case when name = '''+name+''' then apt end) as '+name+','
from t;

And copy the results into the query.