September 10, 2018 at 3:37 pm
Hi,
I am looking to get the same output as in the attached excel. But i am stuck with my dynamic pivot query. Attached excel shows how output should look like.Can someone help with the same.
Thanks in advance.
DECLARE @cols AS NVARCHAR(MAX);
DECLARE @query AS NVARCHAR(MAX);
SELECT @cols = STUFF((SELECT distinct ','
+ QUOTENAME('Count1 '
+ CAST(ROW_NUMBER() OVER(PARTITION BY Period
ORDER BY Period) AS VARCHAR(10)))
FROM #temp1
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 1, '');
SELECT @query = 'SELECT Period, ' + @cols +
'FROM
(
SELECT
Period,
Count1,
''Count1 '' + CAST(ROW_NUMBER()
OVER(PARTITION BY Period
ORDER BY Period) AS VARCHAR(10)) rownum
FROM #temp1
) t
PIVOT
(
MAX(Count1)
FOR rownum IN(' + @cols + ')' +
') p';
execute(@query);
CREATE TABLE #temp1(
Type VARCHAR(6) NOT NULL
,A VARCHAR(4) NOT NULL
,B VARCHAR(5) NOT NULL
,C VARCHAR(14) NOT NULL
,D VARCHAR(9) NOT NULL
,Period VARCHAR(7) NOT NULL
,Count1 INTEGER NOT NULL
,Count2 INTEGER
);
INSERT INTO #temp1(Type,A,B,C,D,Period,Count1,Count2) VALUES ('A type','fs-1','John','Bella,Jacob','Product-1','2018-06',10,NULL);
INSERT INTO #temp1(Type,A,B,C,D,Period,Count1,Count2) VALUES ('A type','fs-1','John','Bella,Jacob','Product-1','2018-07',20,1);
INSERT INTO #temp1(Type,A,B,C,D,Period,Count1,Count2) VALUES ('A type','fs-2','Parth','Mellissa,Jacob','Product-1','2018-06',40,NULL);
INSERT INTO #temp1(Type,A,B,C,D,Period,Count1,Count2) VALUES ('A type','fs-2','Parth','Mellissa,Jacob','Product-1','2018-07',10,5);
September 10, 2018 at 5:49 pm
Post table DDL and inserts of sample data please.
September 11, 2018 at 11:07 am
Perhaps this will do?CREATE TABLE #temp1 (
[Type] VARCHAR(6) NOT NULL,
A VARCHAR(4) NOT NULL,
B VARCHAR(5) NOT NULL,
C VARCHAR(14) NOT NULL,
D VARCHAR(9) NOT NULL,
[Period] VARCHAR(7) NOT NULL,
Count1 INTEGER NOT NULL,
Count2 INTEGER
);
INSERT INTO #temp1
(
[Type],
A,
B,
C,
D,
[Period],
Count1,
Count2
)
VALUES ('A type', 'fs-1', 'John', 'Bella,Jacob', 'Product-1', '2018-06', 10, NULL),
('A type', 'fs-1', 'John', 'Bella,Jacob', 'Product-1', '2018-07', 20, 1),
('A type', 'fs-2', 'Parth', 'Mellissa,Jacob', 'Product-1', '2018-06', 40, NULL),
('A type', 'fs-2', 'Parth', 'Mellissa,Jacob', 'Product-1', '2018-07', 10, 5);
DECLARE @periods AS NVARCHAR(MAX), @query AS NVARCHAR(MAX);
SELECT @periods = (
SELECT DISTINCT
' ' + QUOTENAME([Period] +'_Count1') + ' = MAX(CASE WHEN T.[Period] = ''' + [Period] + ''' THEN T.Count1 END),' + CHAR(13) + CHAR(10) +
' ' + QUOTENAME([Period] +'_Count2') + ' = MAX(CASE WHEN T.[Period] = ''' + [Period] + ''' THEN T.Count2 END),' + CHAR(13) + CHAR(10)
FROM #temp1
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)');
SELECT @periods = LEFT(@periods, LEN(@periods) - 3) + CHAR(13) + CHAR(10);
PRINT @periods;
SELECT @query = '
SELECT
T.[Type],
T.A,
T.B,
T.C,
T.D,
' + @periods + '
FROM #temp1 AS T
GROUP BY
T.[Type],
T.A,
T.B,
T.C,
T.D;';
PRINT @query;
EXECUTE (@query);
DROP TABLE #temp1;
EDIT: Removed unnecessary @cols variable.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 11, 2018 at 11:12 am
FYI, edited my query to remove unnecessary code, and fix resulting query formatting. The reason that you can't use PIVOT here is because you are trying to pivot more than one column, and you can't do that with PIVOT (or with UNPIVOT). Thus you need a dynamic cross tab query.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply