CODEWITHSUNDEEP
sqlsql-server
vali2009
vali2009

Reputation: 35

How to pivot rows into colums dynamically SQL Server

I have a request which returns something like this:

--------------------------
Tool     | Week | Value
--------------------------
Test     | 20   | 3
Sense    | 20   | 2
Test     | 19   | 2

And I want my input to look like this:

-------------------------
Tool     | W20  | W19
-------------------------
Test     | 3    | 2
Sense    | 2    | null

Basically, for every week I need to have a new column. The number of week and of tools is dynamic.

I have tried many things but nothing worked. Anybody have a solution ?

Upvotes: 1

Views: 67

Answers (3)

Krishnraj Rana
Krishnraj Rana

Reputation: 6656

Try this

CREATE table #tst (
Tool varchar(50), [Week] int, Value int
)

insert #tst 
values
('Test', 20, 3),
('Sense', 20,2),
('Test', 19, 2)

Here is the Dynamic Query:

DECLARE @col nvarchar(max), @query NVARCHAR(MAX)

SELECT @col = STUFF((SELECT DISTINCT ',' + QUOTENAME('W' + CAST([Week] as VARCHAR)) 
                from #tst
        FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)') 
    ,1,1,'')

SET @query = '
SELECT *
    FROM   (
        SELECT Tool,
               Value,
               ''W'' + CAST([Week] as VARCHAR) AS WeekNo
        FROM   #tst
    ) t
    PIVOT
    (
        MAX(t.Value)
        FOR WeekNo IN (' + @col + ')
    ) pv
 ORDER by Tool'

 EXEC (@query)

Result

Tool    W20  W19
=================
Sense   2    NULL
Test    3    2

Upvotes: 1

Veljko89
Veljko89

Reputation: 1953

This is how I would do it ... If I understood your question correctly

if object_id('tempdb..#InputTool') is not null drop table #InputTool
create table #InputTool (Tool nvarchar(10), [20] int, [19] int)

insert into #InputTool (Tool, [20], [19])
values 
('Test', 3, 2),
('Sense', 2, null)

declare @cols nvarchar(max)
select @cols = STUFF((SELECT ',' + QUOTENAME(name) 
                        from tempdb.sys.columns 
                        where object_id = object_id('tempdb..#InputTool') 
                        and Column_id > 1
        FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)') 
    ,1,1,'')

declare @sqlquery nvarchar(max) =
'select Tool, Weeks, Value from (
    select * from #InputTool
    ) as it 
    UNPIVOT 
    (
    Value FOR Weeks IN (' + @cols + ')
    ) AS Weeks
order by Weeks desc'

execute (@sqlquery);

Give it a shot and let me know if it worked

Upvotes: 0

mohan111
mohan111

Reputation: 8865

IF OBJECT_ID('tempdb..#temp') IS NOT NULL
    DROP TABLE #temp
    CREATE  TABLE #temp
        ( Tool  varchar(5),  Week  int,  Value  int)
    ;

    INSERT INTO #temp
        ( Tool ,  Week ,  Value )
    VALUES
        ('Test', 20, 3),
        ('Sense', 20, 2),
        ('Test', 19, 2)
    ;




    DECLARE @statement NVARCHAR(max)
    ,@columns NVARCHAR(max),
    @col NVARCHAR(max)


    SELECT @columns = ISNULL(@columns + ', ', '') + N'[' +'w'+ tbl.[Week] + ']'
    FROM (
       SELECT DISTINCT CAST([Week] AS VARCHAR)[Week]
       FROM #temp
       ) AS tbl

    SELECT @statement = 'SELECT *

    FROM
        (
        SELECT 
             Tool ,  ''w''+ CAST(Week AS VARCHAR) week ,  Value
         FROM 
             #Temp

            ) src   
    PIVOT(MAX(Value)for Week in  (' + @columns + ')) as pvt
    '

    EXEC sp_executesql @statement = @statement

Upvotes: 1

Related Questions