How can I do without join to split one columns into multiple columns?

Question

HealthCare :

A	B	ID
582	X	1
582	Y	2
755	123	1
755	456	2
811	abc	1
811	def	2

desire result:

ID	A	B	C	D	E	F
1	582	X	755	123	811	abc
2	582	Y	755	456	811	def

or

ID	582	755	811
1	X	123	abc
2	Y	456	def

I can do like this :

SELECT ID, A, B
FROM HealthCare as a
LEFT JOIN (select A as C, B as D from HealthCare where A = 755) as b
    ON a.ID = b.ID
LEFT JOIN (select A as E, B as F from HealthCare where A = 811) as c
    ON a.ID = c.ID 
WHERE A = 582

Is any method to do this without joins or with less joins?

score 0 · Accepted Answer · answered May 30 '22 at 03:31

If you don't want to use JOIN I think we can try to use the condition aggregate function to make it, try to add the condition in CASE WHEN expression.

SELECT ID,
       MAX(CASE WHEN A = 582 THEN A END) 'A',
       MAX(CASE WHEN A = 582 THEN B END) 'B',
       MAX(CASE WHEN A = 755 THEN A END) 'C',
       MAX(CASE WHEN A = 755 THEN B END) 'D',
       MAX(CASE WHEN A = 582 THEN B END) 'E',
       MAX(CASE WHEN A = 811 THEN B END) 'F'
FROM HealthCare 
GROUP BY ID

sqlfiddle

How can I do without join to split one columns into multiple columns?

1 Answers1