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.
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*/;