How to use unpivot

Question

Post reply

How to use unpivot

sharonrao123

Default port

Points: 1450
More actions
March 2, 2010 at 9:29 pm

#139110

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

Name Class Type Cost sales

Test1 A B 10 John

Test1 C D 20 Paul

Test2 E F 30 Smith

Test2 G H 40 Jake

Test3 I J 50 James

Table B

Name Test1 Test1

Class A C

Type B D

Cost 10 20

sales John Paul

Name Test2 Test2

Class E G

Type F H

Cost 30 40

sales Smith Jake

Name Test3

Class I

Type J

Cost 50

sales James

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

sharonrao123 Default port Points: 1450 More actions · Answer 1

sharonrao123

Default port

Points: 1450

March 2, 2010 at 9:31 pm

#1127523

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.

Ken McKelvey SSCoach Points: 18484 More actions · Answer 2

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