How to pivot table using SQL server?

Question

I have one table name called tblOperation. I would like to get the output row to column.

E.g:

SNO      Operation
---------------------
1        OP10
2        OP20
3        OP20
.         .
.         .
N         N

I need below output format:

OP10      OP20     OP30.....N

How to do it.

Please help me to solve this query.

Given that the number of operation values is large, you might need dynamic SQL for this. Google "dynamic pivot query sql". — Tim Biegeleisen, Jun 15 '16 at 11:10
Possible duplicate of [SQL Server dynamic PIVOT query?](http://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) — David Rushton, Jun 15 '16 at 11:23

score 1 · Answer 1 · answered Jun 15 '16 at 11:25

1

here is the query

SELECT *
FROM piovoit
PIVOT(max(sno) 
      FOR opration IN ([OP10],[OP20],[OP30])) AS PVTTable

answered Jun 15 '16 at 11:25

pavan kumar

322
2
7

score 1 · Answer 2 · answered Jun 15 '16 at 11:36

Better use dynamic SQL in that case:

DECLARE @sql nvarchar(max), 
        @cols nvarchar(max)

SELECT @cols = STUFF((
    SELECT DISTINCT ','+QUOTENAME(Operation) 
    FROM tblOperation
    FOR XML PATH('')),1,1,'')

SELECT @sql = '
SELECT *
FROM tblOperation
PIVOT(
    MAX(sno) FOR Operation IN ('+@cols+')
) AS pvt'

EXEC sp_executesql @sql

score 0 · Answer 3 · answered Jun 15 '16 at 12:01

The syntax of pivot operator like this :

 SELECT <non-pivoted column>,
        [first pivoted column] AS <column name>,
        [second pivoted column] AS <column name>,
        ...
        [last pivoted column] AS <column name>
    FROM
        (<SELECT query that produces the data>)
        AS <alias for the source query>
    PIVOT
    (
        <aggregation function>(<column being aggregated>)
    FOR
    [<column that contains the values that will become column headers>]
        IN ( [first pivoted column], [second pivoted column],
        ... [last pivoted column])
    ) AS <alias for the pivot table>
    <optional ORDER BY clause>;

How to pivot table using SQL server?

3 Answers3