Need help in T-SQL Query (Pivot)

Transact-SQL
1

Hi Team,

Please see my input table and required output table below:

image

I have tried using Pivot query to get the required output but it did not work.
Please help me with the query.

Thanks in advance.

2

If you post consumable SQL, CREATE TABLE statement, INSERT statements with sample data. A picture does us no good.

3

Sorry for the inconvenience. Below are the scripts:

CREATE TABLE InputTable
(
Tname VARCHAR(10) NULL,
Cname VARCHAR(10) NULL
)

INSERT INTO InputTable
SELECT 'A', 'AAA' UNION ALL
SELECT 'A', 'BBB' UNION ALL
SELECT 'A', 'CCC' UNION ALL
SELECT 'A', 'DDD' UNION ALL
SELECT 'A', 'EEE' UNION ALL
SELECT 'B', 'FFF' UNION ALL
SELECT 'B', 'GGG' UNION ALL
SELECT 'B', 'HHH' UNION ALL
SELECT 'C', 'III' UNION ALL
SELECT 'C', 'JJJ' UNION ALL
SELECT 'C', 'KKK' UNION ALL
SELECT 'C', 'LLL'

4

This should do the trick...

WITH 
	cte_AddRN AS (
		SELECT 
			*,
			rn = ROW_NUMBER() OVER (PARTITION BY it.Tname ORDER BY it.Cname)
		FROM
			InputTable it
		)
SELECT 
	A = MAX(CASE WHEN ar.Tname = 'A' THEN ar.Cname END),
	B = MAX(CASE WHEN ar.Tname = 'B' THEN ar.Cname END),
	C = MAX(CASE WHEN ar.Tname = 'C' THEN ar.Cname END)
FROM
	cte_AddRN ar
GROUP BY
	ar.rn;

Results...

A          B          C
---------- ---------- ----------
AAA        FFF        III
BBB        GGG        JJJ
CCC        HHH        KKK
DDD        NULL       LLL
EEE        NULL       NULL.
2 Likes
5

Thank you, Jason, for the query.

1 Like
6

Thank you for the feedback. I'm glad I could help. :slight_smile: