SQLTeam.com | Weblogs | Forums

Help needed with Dynamic pivoting. Getting column repetition error

sql2014

#1

Help needed with Dynamic pivoting. Getting column repetition error when transposing row values columns.

This is the actual data view.

UID GeoBusinessType GeoCountry UIDCountry UIDBusinessType Revenue
123456 Commercial United States United States Commercial $100
123456 Consumer United States United States Consumer $150
123456 Consumer United States United States Consumer $200
678901 Commercial United States United States Commercial $250
678901 Commercial United States United States Commercial $300
123246 Consumer United States United States Consumer $350
123246 Consumer France France Consumer $400
123246 Commercial France France Commercial $450
123246 Commercial France France Commercial $500
246488 Consumer France France Consumer $550
246488 Consumer France France Consumer $600
246488 Consumer France France Consumer $650
246488 Consumer France France Consumer $700
492968 Consumer France France Consumer $750
492968 Consumer Germany Germany Consumer $800
492968 Consumer Germany Germany Consumer $850
985929 Consumer Germany Germany Consumer $900
985929 Consumer Germany Germany Consumer $950
985929 Commercial Germany Germany Commercial $1,000
985929 Commercial Germany Germany Commercial $1,050
197189 Consumer Germany Germany Consumer $1,100
197189 Consumer India India Consumer $1,150
197189 Commercial India India Commercial $1,200
197189 Commercial India India Commercial $1,250

I need the above table column values to be transposed to headers. Since some column will be identical, it fails to execute and I need to call this into my asp.net web form as a grid


#2

Please post an example of desired output using this data


#3

Desired Output. Thank you so much for the reply

GeoBusinessType GeoCountry UIDCountry UIDBusinessType
UID Commercial Consumer United States France Germany India United States France Germany India Commercial Consumer Revenue
123456 GeoBusinessType GeoBusinessType GeoCountry NULL NULL NULL UIDCountry NULL NULL NULL UIDBusinessType UIDBusinessType 450
678901 GeoBusinessType NULL GeoCountry NULL NULL NULL UIDCountry NULL NULL NULL UIDBusinessType NULL 550
123246 GeoBusinessType GeoBusinessType GeoCountry GeoCountry NULL NULL UIDCountry UIDCountry NULL NULL UIDBusinessType UIDBusinessType 1700
246488 GeoBusinessType NULL NULL GeoCountry NULL NULL NULL UIDCountry NULL NULL UIDBusinessType NULL 2500
492968 NULL GeoBusinessType NULL GeoCountry GeoCountry NULL NULL UIDCountry UIDCountry NULL NULL UIDBusinessType 2400
985929 GeoBusinessType NULL NULL NULL GeoCountry NULL NULL NULL UIDCountry NULL UIDBusinessType NULL 3900
197189 GeoBusinessType GeoBusinessType NULL NULL NULL GeoCountry NULL NULL NULL UIDCountry UIDBusinessType UIDBusinessType 4700


#4

i think this is close:

DECLARE @t table(UID int, GeoCountry varchar(30), UIDCountry varchar(30), UIDBusinessType varchar(20), Revenue smallmoney);
INSERT @t ( UID, UIDBusinessType,  GeoCountry, UIDCountry,Revenue )
VALUES
   ( '123456', 'Commercial', 'United States', 'United States', 100 )
 , ( '123456', 'Consumer', 'United States', 'United States', 150 )
 , ( '123456', 'Consumer', 'United States', 'United States', 200 )
 , ( '678901', 'Commercial', 'United States', 'United States', 250 )
 , ( '678901', 'Commercial', 'United States', 'United States', 300 )
 , ( '123246', 'Consumer', 'United States', 'United States', 350 )
 , ( '123246', 'Consumer', 'France', 'France', 400 )
 , ( '123246', 'Commercial', 'France', 'France', 450 )
 , ( '123246', 'Commercial', 'France', 'France', 500 )
 , ( '246488', 'Consumer', 'France', 'France', 550 )
 , ( '246488', 'Consumer', 'France', 'France', 600 )
 , ( '246488', 'Consumer', 'France', 'France', 650 )
 , ( '246488', 'Consumer', 'France', 'France', 700 )
 , ( '492968', 'Consumer', 'France', 'France', 750 )
 , ( '492968', 'Consumer', 'Germany', 'Germany' , 800 )
 , ( '492968', 'Consumer', 'Germany', 'Germany' , 850 )
 , ( '985929', 'Consumer', 'Germany', 'Germany' , 900 )
 , ( '985929', 'Consumer', 'Germany', 'Germany' , 950 )
 , ( '985929', 'Commercial', 'Germany', 'Germany' , 1000 )
 , ( '985929', 'Commercial', 'Germany', 'Germany' , 1050 )
 , ( '197189', 'Consumer', 'Germany', 'Germany', 1100 )
 , ( '197189', 'Consumer', 'India', 'India' , 1150 )
 , ( '197189', 'Commercial', 'India', 'India' , 1200 )
 , ( '197189', 'Commercial', 'India', 'India' , 1250 );
SELECT [UID], UIDBusinessType, [France], [Germany], [India], [United States]
FROM @t t
PIVOT (Sum(Revenue) FOR GeoCountry IN( [France], [Germany], [India], [United States]))P ;

image


#5

@jotorre_riversidedpss.org,
Not even close. I needed the columns in the 1st grid to be tranposed to grid2 so that UID column becomes unique.


#6

Try this...

IF OBJECT_ID('tempdb..#TestData', 'U') IS NOT NULL 
DROP TABLE #TestData;

CREATE TABLE #TestData (
	UID int, 
	GeoBusinessType VARCHAR(30),
	GeoCountry varchar(30), 
	UIDCountry varchar(30), 
	UIDBusinessType varchar(20), 
	Revenue smallmoney);
INSERT #TestData (UID, GeoBusinessType, GeoCountry, UIDCountry, Revenue) VALUES
   ( '123456', 'Commercial', 'United States', 'United States', 100 )
 , ( '123456', 'Consumer', 'United States', 'United States', 150 )
 , ( '123456', 'Consumer', 'United States', 'United States', 200 )
 , ( '678901', 'Commercial', 'United States', 'United States', 250 )
 , ( '678901', 'Commercial', 'United States', 'United States', 300 )
 , ( '123246', 'Consumer', 'United States', 'United States', 350 )
 , ( '123246', 'Consumer', 'France', 'France', 400 )
 , ( '123246', 'Commercial', 'France', 'France', 450 )
 , ( '123246', 'Commercial', 'France', 'France', 500 )
 , ( '246488', 'Consumer', 'France', 'France', 550 )
 , ( '246488', 'Consumer', 'France', 'France', 600 )
 , ( '246488', 'Consumer', 'France', 'France', 650 )
 , ( '246488', 'Consumer', 'France', 'France', 700 )
 , ( '492968', 'Consumer', 'France', 'France', 750 )
 , ( '492968', 'Consumer', 'Germany', 'Germany' , 800 )
 , ( '492968', 'Consumer', 'Germany', 'Germany' , 850 )
 , ( '985929', 'Consumer', 'Germany', 'Germany' , 900 )
 , ( '985929', 'Consumer', 'Germany', 'Germany' , 950 )
 , ( '985929', 'Commercial', 'Germany', 'Germany' , 1000 )
 , ( '985929', 'Commercial', 'Germany', 'Germany' , 1050 )
 , ( '197189', 'Consumer', 'Germany', 'Germany', 1100 )
 , ( '197189', 'Consumer', 'India', 'India' , 1150 )
 , ( '197189', 'Commercial', 'India', 'India' , 1200 )
 , ( '197189', 'Commercial', 'India', 'India' , 1250 );

 UPDATE td SET 
	td.UIDBusinessType = td.GeoBusinessType
 FROM 
 	#TestData td;

--SELECT * FROM #TestData td;

SELECT 
	td.UID,
	GBT_Commercial = MAX(CASE WHEN x.Label = 'GeoBusinessType' AND x.Value = 'Commercial' THEN 'GeoBusinessType' END),
	GBT_Consumer = MAX(CASE WHEN x.Label = 'GeoBusinessType' AND x.Value = 'Consumer' THEN 'GeoBusinessType' END),
	GC_USA = MAX(CASE WHEN x.Label = 'GeoCountry' AND x.Value = 'United States' THEN 'GeoCountry' END),
	GC_France = MAX(CASE WHEN x.Label = 'GeoCountry' AND x.Value = 'France' THEN 'GeoCountry' END),
	GC_Germany = MAX(CASE WHEN x.Label = 'GeoCountry' AND x.Value = 'Germany' THEN 'GeoCountry' END),
	GC_India = MAX(CASE WHEN x.Label = 'GeoCountry' AND x.Value = 'India' THEN 'GeoCountry' END),
	UIDC_USA = MAX(CASE WHEN x.Label = 'UIDCountry' AND x.Value = 'United States' THEN 'UIDCountry' END),
	UIDC_France = MAX(CASE WHEN x.Label = 'UIDCountry' AND x.Value = 'France' THEN 'UIDCountry' END),
	UIDC_Germany = MAX(CASE WHEN x.Label = 'UIDCountry' AND x.Value = 'Germany' THEN 'UIDCountry' END),
	UIDC_India = MAX(CASE WHEN x.Label = 'UIDCountry' AND x.Value = 'India' THEN 'UIDCountry' END),
	UIDBT_Commercial = MAX(CASE WHEN x.Label = 'UIDBusinessType' AND x.Value = 'Commercial' THEN 'UIDBusinessType' END),
	UIDBT_Consumer = MAX(CASE WHEN x.Label = 'UIDBusinessType' AND x.Value = 'Consumer' THEN 'UIDBusinessType' END),
	Revenue = MAX(x.Revenue)
FROM 
	#TestData td
	CROSS APPLY ( VALUES 
						('GeoBusinessType', td.GeoBusinessType, td.Revenue),
						('GeoCountry', td.GeoCountry, 0),
						('UIDCountry', td.UIDCountry, 0),
						('UIDBusinessType', td.UIDBusinessType, 0)
					) x (Label, Value, Revenue)
GROUP BY
	td.UID;

#7

Thank you for responding Jason. I tried this method, Apparently when a fresh data is appended will see more new countries in both geo as well as UID


#8

In that case, here is the dynamic sql version of the previous code...

IF OBJECT_ID('tempdb..#TestData', 'U') IS NOT NULL 
DROP TABLE #TestData;

CREATE TABLE #TestData (
	UID int, 
	GeoBusinessType VARCHAR(30),
	GeoCountry varchar(30), 
	UIDCountry varchar(30), 
	UIDBusinessType varchar(20), 
	Revenue smallmoney);
INSERT #TestData (UID, GeoBusinessType, GeoCountry, UIDCountry, Revenue) VALUES
   ( '123456', 'Commercial', 'United States', 'United States', 100 )
 , ( '123456', 'Consumer', 'United States', 'United States', 150 )
 , ( '123456', 'Consumer', 'United States', 'United States', 200 )
 , ( '678901', 'Commercial', 'United States', 'United States', 250 )
 , ( '678901', 'Commercial', 'United States', 'United States', 300 )
 , ( '123246', 'Consumer', 'United States', 'United States', 350 )
 , ( '123246', 'Consumer', 'France', 'France', 400 )
 , ( '123246', 'Commercial', 'France', 'France', 450 )
 , ( '123246', 'Commercial', 'France', 'France', 500 )
 , ( '246488', 'Consumer', 'France', 'France', 550 )
 , ( '246488', 'Consumer', 'France', 'France', 600 )
 , ( '246488', 'Consumer', 'France', 'France', 650 )
 , ( '246488', 'Consumer', 'France', 'France', 700 )
 , ( '492968', 'Consumer', 'France', 'France', 750 )
 , ( '492968', 'Consumer', 'Germany', 'Germany' , 800 )
 , ( '492968', 'Consumer', 'Germany', 'Germany' , 850 )
 , ( '985929', 'Consumer', 'Germany', 'Germany' , 900 )
 , ( '985929', 'Consumer', 'Germany', 'Germany' , 950 )
 , ( '985929', 'Commercial', 'Germany', 'Germany' , 1000 )
 , ( '985929', 'Commercial', 'Germany', 'Germany' , 1050 )
 , ( '197189', 'Consumer', 'Germany', 'Germany', 1100 )
 , ( '197189', 'Consumer', 'India', 'India' , 1150 )
 , ( '197189', 'Commercial', 'India', 'India' , 1200 )
 , ( '197189', 'Commercial', 'India', 'India' , 1250 );

 UPDATE td SET 
	td.UIDBusinessType = td.GeoBusinessType
 FROM 
 	#TestData td;

--SELECT * FROM #TestData td;

DECLARE 
	@GeoC VARCHAR(8000) = '',
	@UidC VARCHAR(8000) = '',
	@sql VARCHAR(8000) = '',
	@DeBug BIT = 0;

WITH 
	cte_DistinctCountry AS (
		SELECT 
			x.Country,
			ColOrder = ROW_NUMBER() OVER (ORDER BY CASE x.Country WHEN 'United States' THEN 1 WHEN 'France' THEN 2 WHEN 'Germany' THEN 3 WHEN 'India' THEN 4 ELSE 999 END , x.Country)
		FROM
			#TestData td
			CROSS APPLY ( VALUES (td.GeoCountry), (td.UIDCountry) ) x (Country)
		GROUP BY
			x.Country
	)
SELECT 
	@GeoC = CONCAT(@GeoC, '
	[GeoCountry_', dc.Country, '] = MAX(CASE WHEN x.Label = ''GeoCountry'' AND x.Value = ''', dc.Country, ''' THEN ''GeoCountry'' END),'),
	@UidC = CONCAT(@UidC, '
	[UIDCountry_', dc.Country, '] = MAX(CASE WHEN x.Label = ''UIDCountry'' AND x.Value = ''', dc.Country, ''' THEN ''UIDCountry'' END),')
FROM
	cte_DistinctCountry dc
ORDER BY
	dc.ColOrder;

SET @sql = CONCAT(@sql, '
SELECT 
	td.UID,
	GeoBusinessType_Commercial = MAX(CASE WHEN x.Label = ''GeoBusinessType'' AND x.Value = ''Commercial'' THEN ''GeoBusinessType'' END),
	GeoBusinessType_Consumer = MAX(CASE WHEN x.Label = ''GeoBusinessType'' AND x.Value = ''Consumer'' THEN ''GeoBusinessType'' END),',
	@GeoC,
	@UidC, '
	UIDBusinessType_Commercial = MAX(CASE WHEN x.Label = ''UIDBusinessType'' AND x.Value = ''Commercial'' THEN ''UIDBusinessType'' END),
	UIDBusinessType_Consumer = MAX(CASE WHEN x.Label = ''UIDBusinessType'' AND x.Value = ''Consumer'' THEN ''UIDBusinessType'' END),
	Revenue = MAX(x.Revenue)
FROM 
	#TestData td
	CROSS APPLY ( VALUES 
						(''GeoBusinessType'', td.GeoBusinessType, td.Revenue),
						(''GeoCountry'', td.GeoCountry, 0),
						(''UIDCountry'', td.UIDCountry, 0),
						(''UIDBusinessType'', td.UIDBusinessType, 0)
					) x (Label, Value, Revenue)
GROUP BY
	td.UID;');

IF @DeBug = 1
BEGIN 
	PRINT (@sql);
END;
ELSE 
BEGIN
	EXEC (@sql);
END;

#9

This is working @Jason_A_Long. Thank you very much. I just have one question. If I want to call #Testdata table column values from the result output, how do I do that in the same query?


#10

I've updates the previous code to show the actual column names.


#11

The question now is... do you understand how and why Jason's code works so that you can support it in the future?