SQL:如何将表列显示为行? [英] SQL: How to show table column as row?

问题描述

我有桌子:

name quantity
abc     2
abc     3
abc     5

我如何在mssql中查询以在一行中看到结果，例如

How can i query in mssql to see result in one row, like

abc 2 3 5

谢谢.

推荐答案

如果要将这些数据放在单独的列中，则可以使用PIVOT函数:

If you want this data in separate columns, then you can use the PIVOT function:

select *
from 
(
  select name, quantity,
    'Qty_'+cast(row_number() over(partition by name order by quantity) as varchar(10)) rn
  from yourtable
) src
pivot
(
  max(quantity)
  for rn in (Qty_1, Qty_2, Qty_3)
) piv

请参见带演示的SQL提琴.数据透视的结果是:

See SQL Fiddle with Demo. The result of the pivot is:

| NAME | QTY_1 | QTY_2 | QTY_3 |
--------------------------------
|  abc |     2 |     3 |     5 |

如果要将这些数据放在单个列中，则可以使用FOR XML PATH和STUFF():

If you want this data in a single column, then you can use FOR XML PATH and STUFF():

SELECT
     t1.Name,
     STUFF(
         (SELECT ' ' + cast(quantity as varchar(10))
          FROM yourtable t2
          WHERE t1.name = t2.name
          FOR XML PATH (''))
          , 1, 1, '')  AS List
FROM yourtable t1
GROUP BY t1.Name

请参见带演示的SQL提琴.该查询的结果是:

See SQL Fiddle with Demo. The result of this query is:

| NAME |  LIST |
----------------
|  abc | 2 3 5 |

使用数据透视功能，如果数量数量未知，则可以使用动态sql:

With the pivot function, if you have an unknown number of quantity values, then you can use dynamic sql:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(rn) 
                    from
                    (
                      select 'Qty_'+cast(row_number() over(partition by name order by quantity) as varchar(10)) rn
                      from yourtable
                    ) t
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT name,' + @cols + ' from 
             (
                select name, quantity,
                  ''Qty_''+cast(row_number() over(partition by name order by quantity) as varchar(10)) rn
                from yourtable
            ) x
            pivot 
            (
                max(quantity)
                for rn in (' + @cols + ')
            ) p '

execute(@query)

请参见带演示的SQL提琴

这篇关于SQL:如何将表列显示为行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！