How to Convert Rows to Columns in SQL Server Using the PIVOT Function?

Use the PIVOT function in SQL Server to convert the line into columns

Question

The data perspective table in SQL Server provides a powerful method that can be transferred from the row to the list. However, users may encounter challenges when building correct queries.

Solution

Popot function for known column values:

For the predefined column values ​​(the number of weeks in this example), you can directly use the PIVOT function:

Dynamically generated Perspective columns:

<code class="language-sql">select *
from 
(
  select store, week, xCount
  from yt 
) src
pivot
(
  sum(xcount)
  for week in ([1], [2], [3])
) piv;</code>

In order to handle the unknown column value (such as the number of dynamic weeks), the combination of the dynamic SQL and the window function can be used:

Result:

Both methods produce the same result:

<code class="language-sql">DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(Week) 
                    from yt
                    group by Week
                    order by Week
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT store,' + @cols + ' from 
             (
                select store, week, xCount
                from yt
            ) x
            pivot 
            (
                sum(xCount)
                for week in (' + @cols + ')
            ) p '

execute(@query);</code>

| Shop | 1 | 2 | 3 | | 101 | 138 | 282 | 220 |

| 102 | 96 | 212 | 123 |

| 105 | 37 | 78 | 60 |

| 109 | 59 | 97 | 87 |

The above is the detailed content of How to Convert Rows to Columns in SQL Server Using the PIVOT Function?. For more information, please follow other related articles on the PHP Chinese website!