Hi Team,
Please see my input table and required output table below:
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.
Hi Team,
Please see my input table and required output table below:
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.
If you post consumable SQL, CREATE TABLE statement, INSERT statements with sample data. A picture does us no good.
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'
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.
Thank you, Jason, for the query.
Thank you for the feedback. I'm glad I could help.