locked
Pivot Table SQL Server RRS feed

  • Question

  • Hello everyone, 

    I am a newbie about SQL Server. I have problem with Pivot table,

    I have a table PIVOT_TBL (VAL_1, VAL_2).

    The data of this is:

    VAL_1      VAL_2

    10000       12345

    11111       23456

    22222       34567

    I want transform it as:

    VAL_1\VAL_2       12345           23456       34567

    10000                     1                  0              0

    11111                     0                  1              0

    22222                     0                  0              1  

    I have used Pivot Table in SQL Server but it is not active.

    Please help me! 

    Thanks everybody

    Monday, May 14, 2018 4:16 AM

Answers

  • Here you go

    DECLARE @pivot_cols NVARCHAR(1000);
    SELECT @pivot_cols =
            STUFF((SELECT DISTINCT '],COUNT(CASE WHEN val2='+cast(val2 as varchar(20))+' THEN 1 END)[' +cast(val2 as varchar(20))
                   FROM #t
                   ORDER BY '],COUNT(CASE WHEN val2='+cast(val2 as varchar(20))+' THEN 1 END)[' + cast(val2 as varchar(20))
                   FOR XML PATH('')
                   ), 1, 2, '') + ']';

    DECLARE @pivot_query NVARCHAR(2000);
    SET @pivot_query =
    N'SELECT val1,' + @pivot_cols +
      'FROM #t GROUP BY val1 ' 
     
    EXEC(@pivot_query);


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, May 14, 2018 6:29 AM
    Answerer
  • Hello Visakh16, 

    All columns are nvarchar 

    PIVOT_TBL(VAL_1 nvarchar(max), VAL_2 navarchar(max))

    VAL_1     VAL_2

    11111     12345

    22222     

    33333     23456

    44444     12345

    11111              

    ......................

    I have tried it, 

    This is my code:

     

    DECLARE @pivot_cols NVARCHAR(1000);
    SELECT @pivot_cols =
            STUFF((SELECT DISTINCT '],COUNT(CASE WHEN VAL_2='''+cast(VAL_2 as varchar(max))+''' THEN 1 END) ''[' +cast(VAL_2 as varchar(max))
                   FROM PIVOT_TBL
                   ORDER BY '],COUNT(CASE WHEN VAL_2='''+cast(VAL_2 as varchar(max))+''' THEN 1 END) ''[' + cast(VAL_2 as varchar(max))
                   FOR XML PATH('')
                   ), 1, 2, '') + ''']''';

    DECLARE @pivot_query NVARCHAR(2000);
    SET @pivot_query =
    N'SELECT VAL_1,' + @pivot_cols +
      'FROM PIVOT_TBL GROUP BY VAL_1 ' 

    EXEC(@pivot_query);

    and this is error:

    Msg 102, Level 15, State 1, Line 2
    Incorrect syntax near '11111'.

    Thank you very much


    you're not using it as I gave

    you're adding additional quotes in between

    What you need is just this

    DECLARE @pivot_cols NVARCHAR(1000);
    SELECT @pivot_cols =
            STUFF((SELECT DISTINCT '],COUNT(CASE WHEN VAL_2='''+cast(VAL_2 as varchar(max))+''' THEN 1 END) AS [' +cast(VAL_2 as varchar(max))
                   FROM PIVOT_TBL
                   ORDER BY '],COUNT(CASE WHEN VAL_2='''+cast(VAL_2as varchar(max))+''' THEN 1 END) AS [' + cast(VAL_2 as varchar(max))
                   FOR XML PATH('')
                   ), 1, 2, '') + ']';
    
    DECLARE @pivot_query NVARCHAR(2000);
    SET @pivot_query =
    N'SELECT VAL_1,' + @pivot_cols +
      'FROM PIVOT_TBL GROUP BY VAL_1 ' 
    
    EXEC(@pivot_query);


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Monday, May 14, 2018 8:13 AM

All replies

  • Hello everyone, 

    I am a newbie about SQL Server. I have problem with Pivot table,

    I have a table PIVOT_TBL (VAL_1, VAL_2).

    The data of this is:

    VAL_1      VAL_2

    10000       12345

    11111       23456

    22222       34567

    I want transform it as:

    VAL_1\VAL_2       12345           23456       34567

    10000                     1                  0              0

    11111                     0                  1              0

    22222                     0                  0              1  

    I have used Pivot Table in SQL Server but it is not active.

    Please help me! 

    Thanks everybody

    • Merged by Olaf HelperMVP Monday, May 14, 2018 6:09 AM Same question from same OP
    Monday, May 14, 2018 4:20 AM
  • create table #t (val1 int, val2 int)

    insert into #t values (10000,12345)
    insert into #t values (11111,23456)
    insert into #t values (22222,34567)

    SELECT val1,
    COUNT(CASE WHEN val2 = 12345 THEN 1 END) AS [12345],
    COUNT(CASE WHEN val2 = 23456 THEN 1 END) AS [34567],
    COUNT(CASE WHEN val2 = 34567 THEN 1 END) AS [34567]
    FROM #t
    GROUP BY val1;

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, May 14, 2018 4:54 AM
    Answerer
  • Hello Uri Dimant, 

    The first, I wanna sent to you "Thank you for helping".

    The continue, I have a question,

    If the data of table very big, 

    What should I do?

    Thanks and best regards, 

    Monday, May 14, 2018 4:58 AM
  • Hi

    You can use the following code to get the result you are looking for.

    WITH [PIVOT_TBL] AS
    (
    	SELECT 10000 [VAL_1],12345 [VAL_2] UNION ALL
    	SELECT 11111 [VAL_1],23456 [VAL_2] UNION ALL
    	SELECT 22222 [VAL_1],34567
    )
    SELECT * 
    FROM 
    (
    	SELECT [VAL_1],[VAL_2] FROM [PIVOT_TBL]
    ) [Source]
    PIVOT
    (
    	COUNT([VAL_2]) FOR [VAL_2] IN ([12345],[23456],[34567])
    ) [pivotTable]
    If the list of values in [VAL_2] is static then you can "hard code" them like I have done, however, if you require a dynamic list then you can use dynamic SQL as discussed in this article https://www.mssqltips.com/sqlservertip/2783/script-to-create-dynamic-pivot-queries-in-sql-server/

    Monday, May 14, 2018 5:19 AM
  • Hi

    What do you mean a big table? Is that mean you will have thousands of columns (like 123456...)? 


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, May 14, 2018 5:21 AM
    Answerer
  • Hello Uri Dimant, 

    The first, I wanna sent to you "Thank you for helping".

    The continue, I have a question,

    If the data of table very big, 

    What should I do?

    Thanks and best regards, 

    You can use a dynamic logic like this which will work for any number of values in val2 automatically without adding any manual code

    see illustration

    if object_id('tempdb..#t') is not null
    drop table #t
    go
    
    create table #t (val1 int, val2 int)
    
    insert into #t values (10000,12345)
    insert into #t values (11111,23456)
    insert into #t values (22222,34567)
    insert into #t values (22222,34567)
    insert into #t values (22222,12345)
    insert into #t values (33333,34567)
    insert into #t values (33333,45623)
    
    
    
    declare @updlist varchar(max)
    
    set @updlist = stuff((select distinct ',max(case when val2 = ''' + cast(val2 as varchar(20)) + ''' then 1 else 0  end) as [' + cast(val2 as varchar(20)) + ']'
    from #t
    for xml path(''),type).value('.','varchar(max)'),1,1,'')
    
    declare @sql varchar(1000) = 'select val1, ' + @updlist + ' from #t group by val1' 
    exec(@sql)
    
    
    
    
    /*
    Output
    ---------------------------------------
    val1	12345	23456	34567	45623
    ----------------------------------------
    10000	1	0	0	0
    11111	0	1	0	0
    22222	1	0	1	0
    33333	0	0	1	1
    
    
    
    */


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Monday, May 14, 2018 5:36 AM
  • Hi Uri Dimant, 

    Yes, I mean I have a big table:

    VAL_1      VAL_2

    10000       12345

    11111       23456

    22222       34567

    ........

    23424       22342

    Monday, May 14, 2018 5:37 AM
  • Hi Uri Dimant, 

    Yes, I mean I have a big table:

    VAL_1      VAL_2

    10000       12345

    11111       23456

    22222       34567

    ........

    23424       22342

    You can check my suggestion

    It will work fine regardless of any number of values

    see below

    if object_id('tempdb..#t') is not null
    drop table #t
    go
    
    create table #t (val1 int, val2 int)
    
    insert into #t values (10000,12345)
    insert into #t values (11111,23456)
    insert into #t values (22222,34567)
    insert into #t values (22222,34567)
    insert into #t values (22222,12345)
    insert into #t values (33333,34567)
    insert into #t values (33333,45623)
    
    
    
    declare @updlist varchar(max)
    
    set @updlist = stuff((select distinct ',max(case when val2 = ''' + cast(val2 as varchar(20)) + ''' then 1 else 0  end) as [' + cast(val2 as varchar(20)) + ']'
    from #t
    for xml path(''),type).value('.','varchar(max)'),1,1,'')
    
    declare @sql varchar(1000) = 'select val1, ' + @updlist + ' from #t group by val1' 
    exec(@sql)
    
    
    
    
    /*
    Output
    ---------------------------------------
    val1	12345	23456	34567	45623
    ----------------------------------------
    10000	1	0	0	0
    11111	0	1	0	0
    22222	1	0	1	0
    33333	0	0	1	1
    
    
    
    */


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Monday, May 14, 2018 6:27 AM
  • Here you go

    DECLARE @pivot_cols NVARCHAR(1000);
    SELECT @pivot_cols =
            STUFF((SELECT DISTINCT '],COUNT(CASE WHEN val2='+cast(val2 as varchar(20))+' THEN 1 END)[' +cast(val2 as varchar(20))
                   FROM #t
                   ORDER BY '],COUNT(CASE WHEN val2='+cast(val2 as varchar(20))+' THEN 1 END)[' + cast(val2 as varchar(20))
                   FOR XML PATH('')
                   ), 1, 2, '') + ']';

    DECLARE @pivot_query NVARCHAR(2000);
    SET @pivot_query =
    N'SELECT val1,' + @pivot_cols +
      'FROM #t GROUP BY val1 ' 
     
    EXEC(@pivot_query);


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, May 14, 2018 6:29 AM
    Answerer
  • Hi Visakh16

    I want to say "Thank you" to you!

    The query sentence of you is very nice.

    But when I try with data type is nvarchar is error..

    Please help me!

    Thank you and best regards, 

    Monday, May 14, 2018 6:43 AM
  • Thank you Uri Dimant, 

    I am trying use it!

    Thank you very much for helping

    Monday, May 14, 2018 6:44 AM
  • Hi Visakh16

    I want to say "Thank you" to you!

    The query sentence of you is very nice.

    But when I try with data type is nvarchar is error..

    Please help me!

    Thank you and best regards, 

    which column is nvarchar?


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Monday, May 14, 2018 7:04 AM
  • Hello Visakh16, 

    All columns are nvarchar 

    PIVOT_TBL(VAL_1 nvarchar(max), VAL_2 navarchar(max))

    VAL_1     VAL_2

    11111     12345

    22222     

    33333     23456

    44444     12345

    11111              

    ......................

    I have tried it, 

    This is my code:

     

    DECLARE @pivot_cols NVARCHAR(1000);
    SELECT @pivot_cols =
            STUFF((SELECT DISTINCT '],COUNT(CASE WHEN VAL_2='''+cast(VAL_2 as varchar(max))+''' THEN 1 END) ''[' +cast(VAL_2 as varchar(max))
                   FROM PIVOT_TBL
                   ORDER BY '],COUNT(CASE WHEN VAL_2='''+cast(VAL_2 as varchar(max))+''' THEN 1 END) ''[' + cast(VAL_2 as varchar(max))
                   FOR XML PATH('')
                   ), 1, 2, '') + ''']''';

    DECLARE @pivot_query NVARCHAR(2000);
    SET @pivot_query =
    N'SELECT VAL_1,' + @pivot_cols +
      'FROM PIVOT_TBL GROUP BY VAL_1 ' 

    EXEC(@pivot_query);

    and this is error:

    Msg 102, Level 15, State 1, Line 2
    Incorrect syntax near '11111'.

    Thank you very much


    Monday, May 14, 2018 7:35 AM
  • Here you go

    create table #t (val1 VARCHAR(MAX), val2 VARCHAR(MAX))

    insert into #t values ('10000','12345')
    insert into #t values ('11111','23456')
    insert into #t values ('22222',NULL)
    insert into #t values ('44444','36525')
    insert into #t values ('11111',NULL)

    DECLARE @pivot_cols NVARCHAR(1000);
    SELECT @pivot_cols =
            STUFF((SELECT DISTINCT '],COUNT(CASE WHEN VAL2='''+VAL2+''' THEN 1 END) [' +VAL2 
                   FROM #t
                   ORDER BY '],COUNT(CASE WHEN VAL2='''+VAL2 +''' THEN 1 END) [' +VAL2
                   FOR XML PATH('')
                   ), 1, 2, '') + ''']';


    DECLARE @pivot_query NVARCHAR(2000);
    SET @pivot_query =
    N'SELECT VAL1,' + @pivot_cols +
      'FROM #t GROUP BY VAL1 ' 

    EXEC(@pivot_query);


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, May 14, 2018 7:45 AM
    Answerer
  • Hello Visakh16, 

    All columns are nvarchar 

    PIVOT_TBL(VAL_1 nvarchar(max), VAL_2 navarchar(max))

    VAL_1     VAL_2

    11111     12345

    22222     

    33333     23456

    44444     12345

    11111              

    ......................

    I have tried it, 

    This is my code:

     

    DECLARE @pivot_cols NVARCHAR(1000);
    SELECT @pivot_cols =
            STUFF((SELECT DISTINCT '],COUNT(CASE WHEN VAL_2='''+cast(VAL_2 as varchar(max))+''' THEN 1 END) ''[' +cast(VAL_2 as varchar(max))
                   FROM PIVOT_TBL
                   ORDER BY '],COUNT(CASE WHEN VAL_2='''+cast(VAL_2 as varchar(max))+''' THEN 1 END) ''[' + cast(VAL_2 as varchar(max))
                   FOR XML PATH('')
                   ), 1, 2, '') + ''']''';

    DECLARE @pivot_query NVARCHAR(2000);
    SET @pivot_query =
    N'SELECT VAL_1,' + @pivot_cols +
      'FROM PIVOT_TBL GROUP BY VAL_1 ' 

    EXEC(@pivot_query);

    and this is error:

    Msg 102, Level 15, State 1, Line 2
    Incorrect syntax near '11111'.

    Thank you very much


    you're not using it as I gave

    you're adding additional quotes in between

    What you need is just this

    DECLARE @pivot_cols NVARCHAR(1000);
    SELECT @pivot_cols =
            STUFF((SELECT DISTINCT '],COUNT(CASE WHEN VAL_2='''+cast(VAL_2 as varchar(max))+''' THEN 1 END) AS [' +cast(VAL_2 as varchar(max))
                   FROM PIVOT_TBL
                   ORDER BY '],COUNT(CASE WHEN VAL_2='''+cast(VAL_2as varchar(max))+''' THEN 1 END) AS [' + cast(VAL_2 as varchar(max))
                   FOR XML PATH('')
                   ), 1, 2, '') + ']';
    
    DECLARE @pivot_query NVARCHAR(2000);
    SET @pivot_query =
    N'SELECT VAL_1,' + @pivot_cols +
      'FROM PIVOT_TBL GROUP BY VAL_1 ' 
    
    EXEC(@pivot_query);


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Monday, May 14, 2018 8:13 AM
  • Thank you Visakh16,

    Your code very very nice!

    Thank you very much!

    Monday, May 14, 2018 8:34 AM
  • Hi Visakh16, 

    Sorry for bothering you

    Cause my poor knowledge about sql server.

    I have a question that:

    If I select this sentence very long and I have used DECLARE @pivot_query NVARCHAR(MAX);

    SELECT DISTINCT '],COUNT(CASE WHEN VAL_2='''+cast(VAL_2 as varchar(max))+''' THEN 1 END) AS [' +cast(VAL_2 as varchar(max))
                   FROM PMD_LOOKUP_TBL
                   ORDER BY '],COUNT(CASE WHEN VAL_2='''+cast(VAL_2 as varchar(max))+''' THEN 1 END) AS [' + cast(VAL_2 as varchar(max))
                   FOR XML PATH('')

    I have a problem cause result so long and I can't execute the query.

    Can you answer my questions, please?

    Thanks and best regards,

    Monday, May 14, 2018 8:57 AM
  • Hi Visakh16, 

    Sorry for bothering you

    Cause my poor knowledge about sql server.

    I have a question that:

    If I select this sentence very long and I have used DECLARE @pivot_query NVARCHAR(MAX);

    SELECT DISTINCT '],COUNT(CASE WHEN VAL_2='''+cast(VAL_2 as varchar(max))+''' THEN 1 END) AS [' +cast(VAL_2 as varchar(max))
                   FROM PMD_LOOKUP_TBL
                   ORDER BY '],COUNT(CASE WHEN VAL_2='''+cast(VAL_2 as varchar(max))+''' THEN 1 END) AS [' + cast(VAL_2 as varchar(max))
                   FOR XML PATH('')

    I have a problem cause result so long and I can't execute the query.

    Can you answer my questions, please?

    Thanks and best regards,

    Sorry didnt understand

    what is too long?

    NVARCHAR(max) can handle very long strings

    Also you dont need to use nvarchar unless you deal with unicode data

    so varchar would be sufficient


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Monday, May 14, 2018 9:36 AM