SQL Pivot Table Not Putting Results In One Line

Question

I am trying to create a pivot table in SQL. My results are not being consolidated onto one line per TID as I had hoped. This example should make my issue clear:

    Results:

    TID NS_AM   AS_AM   NS_DB   AS_DB
    TID 1a  971     947     
    TID 2   807     974     
    TID 1a                  954     910
    TID 2                   931     904


    Desired Results:

    TID     NS_AM   AS_AM   NS_DB   AS_DB
    TID 1a  971     947     954     910
    TID 2   807     974     931     904

EDIT: This is derived from a large dynamic query. I couldn't put that query in because it would make the question really confusing. I have to use pivot/unpivot.

Any help would be greatly appreciated. Below is sample data and the query that generated my results.

As you can see, the AM items are being consolidated on one line and the DB items are being consolidated on one line but they are not reporting all the results as I had hoped.

            drop table if exists  mock_data;
             create table MOCK_DATA (
                tid VARCHAR(50),
                plantype VARCHAR(50),
                ns VARCHAR(50),
                [as] VARCHAR(50)
            );
            insert into MOCK_DATA (tid, plantype, ns, [as]) values ('TID 1a', 'DB', '112', '048');
            insert into MOCK_DATA (tid, plantype, ns, [as]) values ('TID 1a', 'DB', '142', '889');
            insert into MOCK_DATA (tid, plantype, ns, [as]) values ('TID 1a', 'DB', '887', '668');
            insert into MOCK_DATA (tid, plantype, ns, [as]) values ('TID 1a', 'DB', '093', '910');
            insert into MOCK_DATA (tid, plantype, ns, [as]) values ('TID 1a', 'DB', '954', '266');
            insert into MOCK_DATA (tid, plantype, ns, [as]) values ('TID 1a', 'DB', '822', '201');
            insert into MOCK_DATA (tid, plantype, ns, [as]) values ('TID 1a', 'AM', '234', '083');
            insert into MOCK_DATA (tid, plantype, ns, [as]) values ('TID 1a', 'AM', '527', '716');
            insert into MOCK_DATA (tid, plantype, ns, [as]) values ('TID 1a', 'AM', '662', '168');
            insert into MOCK_DATA (tid, plantype, ns, [as]) values ('TID 1a', 'AM', '795', '947');
            insert into MOCK_DATA (tid, plantype, ns, [as]) values ('TID 1a', 'AM', '971', '588');
            insert into MOCK_DATA (tid, plantype, ns, [as]) values ('TID 1a', 'AM', '755', '234');
            insert into MOCK_DATA (tid, plantype, ns, [as]) values ('TID 1a', 'AM', '199', '603');
            insert into MOCK_DATA (tid, plantype, ns, [as]) values ('TID 1a', 'AM', '164', '362');
            insert into MOCK_DATA (tid, plantype, ns, [as]) values ('TID 1a', 'AM', '943', '462');
            insert into MOCK_DATA (tid, plantype, ns, [as]) values ('TID 1a', 'AM', '971', '164');
            insert into MOCK_DATA (tid, plantype, ns, [as]) values ('TID 1a', 'AM', '594', '822');
            insert into MOCK_DATA (tid, plantype, ns, [as]) values ('TID 1a', 'AM', '467', '478');
            insert into MOCK_DATA (tid, plantype, ns, [as]) values ('TID 1a', 'AM', '064', '591');
            insert into MOCK_DATA (tid, plantype, ns, [as]) values ('TID 1a', 'AM', '639', '298');
            insert into MOCK_DATA (tid, plantype, ns, [as]) values ('TID 2', 'AM', '186', '797');
            insert into MOCK_DATA (tid, plantype, ns, [as]) values ('TID 2', 'AM', '226', '369');
            insert into MOCK_DATA (tid, plantype, ns, [as]) values ('TID 2', 'AM', '036', '272');
            insert into MOCK_DATA (tid, plantype, ns, [as]) values ('TID 2', 'AM', '807', '197');
            insert into MOCK_DATA (tid, plantype, ns, [as]) values ('TID 2', 'AM', '167', '402');
            insert into MOCK_DATA (tid, plantype, ns, [as]) values ('TID 2', 'AM', '477', '047');
            insert into MOCK_DATA (tid, plantype, ns, [as]) values ('TID 2', 'AM', '262', '974');
            insert into MOCK_DATA (tid, plantype, ns, [as]) values ('TID 2', 'AM', '268', '282');
            insert into MOCK_DATA (tid, plantype, ns, [as]) values ('TID 2', 'AM', '508', '069');
            insert into MOCK_DATA (tid, plantype, ns, [as]) values ('TID 2', 'DB', '303', '528');
            insert into MOCK_DATA (tid, plantype, ns, [as]) values ('TID 2', 'DB', '747', '325');
            insert into MOCK_DATA (tid, plantype, ns, [as]) values ('TID 2', 'DB', '293', '614');
            insert into MOCK_DATA (tid, plantype, ns, [as]) values ('TID 2', 'DB', '886', '221');
            insert into MOCK_DATA (tid, plantype, ns, [as]) values ('TID 2', 'DB', '652', '365');
            insert into MOCK_DATA (tid, plantype, ns, [as]) values ('TID 2', 'DB', '931', '904');
            insert into MOCK_DATA (tid, plantype, ns, [as]) values ('TID 2', 'DB', '089', '662');
            insert into MOCK_DATA (tid, plantype, ns, [as]) values ('TID 2', 'DB', '922', '497');
            insert into MOCK_DATA (tid, plantype, ns, [as]) values ('TID 2', 'DB', '722', '328');
            insert into MOCK_DATA (tid, plantype, ns, [as]) values ('TID 2', 'DB', '386', '324');
            insert into MOCK_DATA (tid, plantype, ns, [as]) values ('TID 2', 'DB', '402', '552');



            SELECT [TID],
                   IsNull([NS_AM], '') AS [NS_AM],
                   IsNull([AS_AM], '') AS [AS_AM] 
            FROM
            (
                SELECT [TID],
                       [PlanType],
                       col+'_'+CAST(PlanType AS VARCHAR(50)) col,
                       value
                FROM
            (
                SELECT PlanType,
                       [TID],
                       CAST(NS AS VARCHAR(100)) AS NS,
                       CAST(AS AS VARCHAR(100)) AS AS 

                FROM #temp
            ) s UNPIVOT(value FOR col IN(NS,
                                         AS )) unpiv
            ) src PIVOT(MAX(value) FOR col IN([NS_AM],
                                              [AS_AM], 
                                              [NS_DB],
                                              [AS_DB] 

             )) p;

Thank you so much in advance.

Luis Cazares · Accepted Answer · 2019-03-26T18:49:39.573

2

This could be so much simple using CROSS TABS instead of unpivoting and pivoting the data.

SELECT TID, 
       MAX( CASE WHEN PlanType = 'AM' THEN [ns] END) AS NS_AM,
       MAX( CASE WHEN PlanType = 'AM' THEN [as] END) AS AS_AM,
       MAX( CASE WHEN PlanType = 'DB' THEN [ns] END) AS NS_DB,
       MAX( CASE WHEN PlanType = 'DB' THEN [as] END) AS AS_DB
FROM MOCK_DATA
GROUP BY TID;

If you want to use dynamic code, it's not that hard to convert. It just becomes an issue of copy-paste-edit for each column that you need to handle, and the code will add as many values for plantype as needed.

DECLARE @SQL nvarchar(MAX)

SELECT @SQL = N'SELECT TID' + CHAR(10) 
            + ( SELECT DISTINCT REPLACE( REPLACE(
                        CHAR(9) + ',MAX( CASE WHEN PlanType = <<quotedplantype>> THEN [ns] END) AS NS_<<plantype>>' + CHAR(10)
                      + CHAR(9) + ',MAX( CASE WHEN PlanType = <<quotedplantype>> THEN [as] END) AS AS_<<plantype>>' + CHAR(10)
                        , '<<quotedplantype>>', QUOTENAME(plantype, '''')), '<<plantype>>', plantype)
            FROM MOCK_DATA
            FOR XML PATH(''), TYPE).value('./text()[1]', 'varchar(max)') + 
           + N'FROM MOCK_DATA' + NCHAR(10)
           + N'GROUP BY TID;'

EXEC sp_executesql @SQL /*, @params_def, @param1, @param2,..., @paramN*/;

edited Mar 26 '19 at 18:49

answered Mar 26 '19 at 18:28

Luis Cazares

3,495
8
22

I know you are right but it's a dynamic query that I stripped down to create this question. I definitely would have gone there first. Thanks. – Missy Mar 26 '19 at 18:32
1

This can also be done in a dynamic way. But I couldn't guess your needs. I'll edit this post to add the dynamic option. – Luis Cazares Mar 26 '19 at 18:36
Thank you, Luis. There are many more columns than NS and AS and many more options that AM and DB that's why I used the idea here https://stackoverflow.com/questions/15081290/multi-column-dynamic-pivot-table but it's not working for me. – Missy Mar 26 '19 at 18:39
1

I hope that with the edit, you can actually get the result you need. – Luis Cazares Mar 26 '19 at 19:03
Thank you, Luis. I can't believed that worked. I so appreciate not just solving my problem but teaching me this new avenue. So much simpler the pivot unpivot mystery. Gladly marked solved by you :) – Missy Mar 28 '19 at 16:24

Himanshu · Answer 2 · 2019-03-26T18:32:46.910

1

Null is used as a substitute (as some large random number for some missing value )thats why its so unique as well. But here as aggregate functions do ignore nulls except for count as per the standard though you can have a check also by using both min and max

You can have a group by and take min/max if by my assumption theres only null values different as above

  Select TID, min(NS_AM),  min( AS_AM),   
   min(NS_DB),  min(AS_DB) Group by 
    TID.

edited Mar 26 '19 at 18:32

answered Mar 26 '19 at 18:23

Himanshu

3,830
2
10
29

It's a dynamic query so I can't do it that way. Good thought though. Thanks. – Missy Mar 26 '19 at 18:33
Is it got generated via some report? – Himanshu Mar 26 '19 at 18:35
I am generating a dynamic query like these examples: https://stackoverflow.com/questions/14694691/sql-server-pivot-table-with-multiple-column-aggregates and https://stackoverflow.com/questions/15081290/multi-column-dynamic-pivot-table – Missy Mar 26 '19 at 18:37

score 1 · Answer 3 · answered Mar 26 '19 at 18:31

1

Try to add "Order by TID" to your PIVOT/UNPIVOT expressions.

answered Mar 26 '19 at 18:31

Oly

302
1
8

Oly -- can you tell me where? I'm getting syntax errors every where I try. Thx :) – Missy Mar 26 '19 at 18:36
1

The very last statement of your select block https://learn.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-2017 – Oly Mar 26 '19 at 19:40
Thanks for trying. I couldn't get it :( – Missy Mar 26 '19 at 21:04

Kemal AL GAZZAH · Answer 4 · 2019-03-26T19:09:30.433

1

You can get the desired data by doing an inner join as follows, without pivot !

select v1.tid,v1.m_ns[Ns_Am],v1.m_as[As_Am],v2.m_ns[NS_DB],V2.m_As[AS_DB] from(
select tid,plantype,max(ns)[m_ns],max([as]) [m_as]  from MOCK_DATA group by tid,plantype) v1
inner join
(select tid,plantype,max(ns)[m_ns],max([as]) [m_as]  from MOCK_DATA group by tid,plantype)v2
on v1.tid=v2.tid and v1.plantype='Am' and  v2.plantype='DB'

You can also use this script which you can make dynamic (give me some time, I will do it for you !)

select v1.tid,v1.[am] [ns_am],v1.[db][ns_db],v2.[am] [as_am],v2.[db][as_db] from
(select * from(
select tid,ns,[PlanType] from MOCK_DATA) s
pivot
(max(ns) for [PlanType] in ([AM],[DB])  )s
) v1
inner join
(
select * from(
select tid,[as],[PlanType] from MOCK_DATA) s
pivot
(max([as]) for [PlanType] in ([AM],[DB])  )s) v2

on v1.tid=v2.tid

edited Mar 26 '19 at 19:09

answered Mar 26 '19 at 18:42

Kemal AL GAZZAH

967
6
15

Thank you but I can't. The query is dynamically generated and there are many columns and plan type possibilities. Thank you though. – Missy Mar 26 '19 at 18:46
Kemal -- don't. I truly thank you but my script is unwieldy. The number of joins would have to be dynamic. I think the pivot/unpivot is the only way to go. I upvoted you for your efforts. – Missy Mar 26 '19 at 19:17
1

Missy, there is a way to do it dynamically without joins, using a cursor, if you wish, I will post the script. – Kemal AL GAZZAH Mar 27 '19 at 07:08

score 1 · Answer 5 · answered Mar 27 '19 at 08:44

Here is a dynamic solution as promised, using cursors, and not joins It adds dynamically columns to the final table and update the data inside the cursor loop

IF EXISTS(SELECT 1 FROM sysobjects where name='myMOCK_DATA') drop table myMOCK_DATA
create table myMOCK_DATA (tid varchar(10));insert into  myMOCK_DATA(tid) select distinct(tid) from MOCK_DATA
declare mycursor cursor for
select tid,plantype,max(ns)[m_ns],max([as])[m_as] from MOCK_DATA group by tid,plantype
declare @plantype as varchar(2);declare @tid as varchar(10);declare @m_ns as int;declare @m_as as int
declare @script as varchar(max);open mycursor
fetch mycursor into @tid,@plantype,@m_ns,@m_as
while @@fetch_status=0
begin
set @script='IF not EXISTS(SELECT 1 FROM sys.columns  WHERE Name = ''NS_'+@plantype +''' AND Object_ID = Object_ID(N''myMOCK_DATA''))
BEGIN
    alter table myMOCK_DATA add NS_'+@plantype +' integer
END'
print @script;exec(@script)
set @script='IF not EXISTS(SELECT 1 FROM sys.columns 
          WHERE Name = ''AS_'+@plantype + ''' AND Object_ID = Object_ID(N''myMOCK_DATA''))
BEGIN
    alter table myMOCK_DATA add AS_'+@plantype +' integer
END'
print @script;exec(@script)
set @script='update myMOCK_DATA set NS_'+@plantype+'='+convert(varchar(10),@m_ns)+ ' where tid='''+@tid+''''
print @script;exec(@script)
set @script='update myMOCK_DATA set AS_'+@plantype+'='+convert(varchar(10),@m_as)+ ' where tid='''+@tid+''''
print @script;exec(@script)
fetch mycursor into @tid,@plantype,@m_ns,@m_as
end
close mycursor
deallocate mycursor
select * from myMOCK_DATA

Thanks so much Kemal. I worked with it a bit and it could work. The problems are that it is so slow for the amount of data I have. Fetch and likely alter table for my > 300,000 rows really make me want to try to find a way to get my original solution to work properly. It's not that I don't appreciate the work and definitely you found a smart way - I just can't use it right now for this problem. Thank you, my friend. I'll look up your profile and upvote you a bit for your efforts. — Missy, Mar 28 '19 at 16:08

score 1 · Answer 6 · answered Mar 22 '21 at 12:24

Try to selet only needed columns to produce header:

SELECT   [1]
     ,   [2]
     ,   [3]
     ,   [4]
     ,   [5]
     FROM (SELECT klassID, KlassText FROM tblKlass) s
     PIVOT (
         MAX(KlassText) 
         FOR klassID IN ([1], [2], [3], [4], [5])
     ) p

SQL Pivot Table Not Putting Results In One Line

6 Answers6