Transposing Data

Transact-SQL
1

hi

This is my sample table data in this format:

COL1 COL2 COL3 COL4 COL5 COL6
CatA 10 2 5 15 16
CatB 9 3 4 11 13
CatC 20 22 25 15 16

There are many categories and columns but to simplify it with this table data.

How do I attain the output as this:

COL1 COL2
CatA 10
CatA 2
CatA 5
CatA 15
CatA 16
CatB 9
CatB 3
CatB 4
CatB 11
CatB 13
CatC 20
CatC 22
CatC 25
CatC 15
CatC 16

Sorry for the trouble and thanks a lot

2

Have you tried UNPIVOT?

3

Or, you can use the method @gbritton used in this post, like this:

CREATE TABLE #tmp 
	(col1 VARCHAR(32), col2 INT, col3 INT, col4 INT, col5 INT, col6 INT);
INSERT INTO #tmp VALUES 
	('CatA',10,2,5,15,16),
	('CatB',9,3,4,11,13), 
	('CatC',20,22,25,15,16);

SELECT
	c1.col1,c2.col2
FROM
	#tmp
	CROSS APPLY
	(
		VALUES(col1)
	) c1(col1)
	CROSS APPLY
	(
		VALUES (col2),(col3),(col4),(col5),(col6)
	) c2(col2);

DROP TABLE #tmp;
1 Like
4

So much better than UNPIVOT!

5

Indeed!! I always have to THINK before I write an unpivot query; it just doesn't come naturally to me. This on the other hand, makes so much more intuitive sense. Thank you for posting it.

6

Hi Please find ur solution

SELECT u.col1
	,u.abc
	,u.xyz
FROM #abc s
UNPIVOT (xyz FOR abc IN (
			col2
			,col3
			,col4
			,col5
			,col6
			)) u

/*
DROP TABLE #abc
CREATE TABLE #abc
(
col1 VARCHAR(10) NULL, col2 INT NULL, col3 INT NULL, col4 INT NULL, col5 INT NULL, col6 INT NULL
)

INSERT INTO #abc
SELECT 'CatA',
'10',
'2',
'5',
'15',
'16'

INSERT INTO #abc
SELECT 'CatB',
'9',
'3',
'4',
'11',
'13'

INSERT INTO #abc
SELECT 'CatC',
'20',
'22',
'25',
'15',
'16'
*/

7

Go with James K's solution. Much nicer and asymptotically much faster

8

Nice JamesK.
Question - why is it that cross apply does not apply to all c1 all c2 values:
CatA - 10
CatA - 9
CatA - 20
CatA - 2
CatA - 3
CatA - 22.....

9

the cross apply is executed once per input row. the first CA just selects one column, so it's 1 input row to 1 output. The second selects 5 columns so it becomes 1 to 5.

make sense?

2 Likes
10

hi everyone.

Thanks a lot and so much for replying. Thanks once again.

11

Thank you @gbritton. It makes sense if I can remember it. :relaxed: