March 2, 2010 at 9:29 pm
hello All,
I will get straight to business. Any suggestions are higly appreciated and many thanks for the guys who spend their valuable time.
Situtation : Using unpivot is it possible to get Table B from Table A. I am have to group the rows with same name and display the data as in Table B. The first column will be column names followed by how many rows we have with same name, for example Test1 has two rows, Test 3 has one row.
Table A
NameClassTypeCostsales
Test1AB10John
Test1CD20Paul
Test2EF30Smith
Test2GH40Jake
Test3IJ50James
Table B
NameTest1Test1
ClassAC
TypeBD
Cost1020
salesJohnPaul
NameTest2Test2
ClassEG
TypeFH
Cost3040
salesSmithJake
NameTest3
ClassI
TypeJ
Cost50
salesJames
March 2, 2010 at 9:31 pm
hello All,
I have been doing that from Table A in excel using tranpose function, trying to find a way on how to do that using t-sql.
Thanks in Advace.
Sharon.
March 3, 2010 at 4:45 am
The only order in SQL is what is specified in the ORDER BY clause. This means that you will have to some of the work
in the front end. An UNPIVOT followed by a PIVOT should nearly get the result you want:
-- *** Test Data ***
DECLARE @t TABLE
(
Name varchar(20) NOT NULL
,Class char(1) NOT NULL
,[Type] char(1) NOT NULL
,Cost int NOT NULL
,Sales varchar(20) NOT NULL
)
INSERT INTO @t
SELECT 'Test1', 'A', 'B', '10', 'John' UNION ALL
SELECT 'Test1', 'C', 'D', '20', 'Paul' UNION ALL
SELECT 'Test2', 'E', 'F', '30', 'Smith' UNION ALL
SELECT 'Test2', 'G', 'H', '40', 'Jake' UNION ALL
SELECT 'Test3', 'I', 'J', '50', 'James'
-- *** End Test Data ***
SELECT Name, Attribute, [1] AS Value1, [2] AS Value2
FROM
(
SELECT Name, Attribute, RowNum, [Value]
FROM
(
SELECT Name
,CAST(Class AS varchar(20)) AS Class
,CAST([Type] AS varchar(20)) AS [Type]
,CAST(Cost AS varchar(20)) AS Cost
,Sales
,ROW_NUMBER() OVER (PARTITION BY Name ORDER BY Name) AS RowNum
FROM @t
) P
UNPIVOT
(
[Value] FOR Attribute IN
(Class, [Type], Cost, Sales)
) AS UP
) AS UP1
PIVOT
(
MAX([Value])
FOR RowNum IN ([1], [2])
) AS P1
ORDER BY Name
,CASE Attribute
WHEN 'Class' THEN 1
WHEN 'Type' THEN 2
WHEN 'Cost' THEN 3
ELSE 4
END
March 3, 2010 at 8:15 pm
Thanks a lot it worked like a charm. You are a champ.
😀
March 3, 2010 at 10:16 pm
hi,
Can i ask you one more question:
Can you please explain the logic as to using both pivot and unpivot?
Thanks, Sharon
March 4, 2010 at 2:09 am
You need to UNPIVOT to get the attribute and then PIVOT to get the attribute values in the same row.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply