CODEWITHSUNDEEP
sql-server
dhamo
dhamo

Reputation: 181

How to pivot table using SQL server?

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.

Upvotes: 2

Views: 559

Answers (3)

shahid zaman
shahid zaman

Reputation: 122

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>;

Upvotes: 0

gofr1
gofr1

Reputation: 15977

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

Upvotes: 1

pavan kumar
pavan kumar

Reputation: 322

enter image description here here is the query

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

Upvotes: 1

Related Questions