January 22, 2016 at 5:03 am
Hi folks,
I'm facing problem while using cross tab, what my problem is:
sample data:
DCI_SNODCI_COMPOUNDDCI_BLOCKDCI_PARAMETERDCI_DESCRIPTIONDCI_UNIT
1 U1001AI1 AI010301PNT MILL A CURRENT UNIT1
2 U1001AI2 AI010201PNT MILL B CURRENT UNIT2
3 U1001AI3 AI010401PNT MILL A CURRENT UNIT1
and I want to convert this into:
DCI_SNO 1 2 3
DCI_COMPOUND U1001AI1 U1001AI2 U1001AI3
DCI_BLOCK AI010301 AI010201 AI010401
DCI_PARAMETER PNT PNT PNT
DCI_DESCRIPTION MILL A CURRENT MILL B CURRENT MILL A CURRENT
DCI_UNIT UNIT1 UNIT2 UNIT1
I've searched over net and read some articles but I'm not able to achieve this, Please help me
one more thing in sample data I used a table that will contain only 3 rows but in real scenario it will contain large no of rows, so the no of columns will be much more than as expected
Thanks in advance
January 22, 2016 at 5:06 am
Please post CREATE TABLE statement for the table and INSERT statements with your sample data, plus expected results for that sample data.
Also, why exactly do you need this? This kind of transformation is often better handled at the client.
January 22, 2016 at 9:06 am
vinod.joshi040 (1/22/2016)
Hi folks,I'm facing problem while using cross tab, what my problem is:
sample data:
DCI_SNODCI_COMPOUNDDCI_BLOCKDCI_PARAMETERDCI_DESCRIPTIONDCI_UNIT1 U1001AI1 AI010301PNT MILL A CURRENT UNIT1
2 U1001AI2 AI010201PNT MILL B CURRENT UNIT2
3 U1001AI3 AI010401PNT MILL A CURRENT UNIT1
and I want to convert this into:
DCI_SNO 1 2 3DCI_COMPOUND U1001AI1 U1001AI2 U1001AI3
DCI_BLOCK AI010301 AI010201 AI010401
DCI_PARAMETER PNT PNT PNT
DCI_DESCRIPTION MILL A CURRENT MILL B CURRENT MILL A CURRENT
DCI_UNIT UNIT1 UNIT2 UNIT1
I've searched over net and read some articles but I'm not able to achieve this, Please help me
one more thing in sample data I used a table that will contain only 3 rows but in real scenario it will contain large no of rows, so the no of columns will be much more than as expected
Thanks in advance
You basically need to unpivot your columns and then pivot them again.
There's a lot of things happening in the code that I'm posting, so please be sure to understand them, I'll include some references at the end, but feel free to ask any question that you might have.
CREATE TABLE #Test(
DCI_SNO int,
DCI_COMPOUND varchar(10),
DCI_BLOCK varchar(10),
DCI_PARAMETER varchar(10),
DCI_DESCRIPTION varchar(50),
DCI_UNIT varchar(10)
);
INSERT INTO #Test VALUES
(1, 'U1001AI1', 'AI010301', 'PNT', 'MILL A CURRENT', 'UNIT1'),
(2, 'U1001AI2', 'AI010201', 'PNT', 'MILL B CURRENT', 'UNIT2'),
(3, 'U1001AI3', 'AI010401', 'PNT', 'MILL A CURRENT', 'UNIT1');
--Static solution
SELECT label
,MAX(CASE WHEN DCI_SNO = 1 THEN value END) AS [1]
,MAX(CASE WHEN DCI_SNO = 2 THEN value END) AS [2]
,MAX(CASE WHEN DCI_SNO = 3 THEN value END) AS [3]
FROM #Test
CROSS APPLY (VALUES(1, 'DCI_SNO', CAST(DCI_SNO AS varchar(10))),
(2, 'DCI_COMPOUND', DCI_COMPOUND),
(3, 'DCI_BLOCK', DCI_BLOCK) ,
(4, 'DCI_PARAMETER', DCI_PARAMETER),
(5, 'DCI_DESCRIPTION', DCI_DESCRIPTION),
(6, 'DCI_UNIT', DCI_UNIT))x(labelorder, label, value)
GROUP BY label, labelorder
ORDER BY labelorder;
--Dynamic solution
DECLARE @SQL nvarchar(max)
SET @SQL = 'SELECT label ' + CHAR( 10)
+ (SELECT ',MAX(CASE WHEN DCI_SNO = ' + CAST(DCI_SNO AS varchar(10)) + ' THEN value END) AS ' + QUOTENAME(DCI_SNO) + CHAR(10)
FROM #Test
FOR XML PATH(''), TYPE).value('./text()[1]', 'nvarchar(max)')
+ 'FROM #Test
CROSS APPLY (VALUES(1, ''DCI_SNO'', CAST(DCI_SNO AS varchar(10))),
(2, ''DCI_COMPOUND'', DCI_COMPOUND),
(3, ''DCI_BLOCK'', DCI_BLOCK) ,
(4, ''DCI_PARAMETER'', DCI_PARAMETER),
(5, ''DCI_DESCRIPTION'', DCI_DESCRIPTION),
(6, ''DCI_UNIT'', DCI_UNIT))x(labelorder, label, value)
GROUP BY label, labelorder
ORDER BY labelorder;'
EXEC sp_executesql @SQL;
GO
DROP TABLE #Test
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply