Converting Rows to Columns in SQL
Posted on by
There was this table,
ORG_ID LEGACY_ORG_CODE ------ ------------------ 1001 8909 1001 12323 1002 80909 1002 78798 1002 09009
and the requirement was to get the concatenated legacy_org_code like
ORG_ID LEGACY_ORG_CODE ------ ------------------ 1001 8909, 12323 1002 80909, 78798, 09009
The solution to this is:
SELECT PL.ORG_ID, (SELECT LEGACY_ORG_CODE + ',' FROM SGT_ORG_PLAN ORGPL WHERE ORGPL.ORG_ID = PL.ORG_ID ORDER BY LEGACY_ORG_CODE FOR XML PATH('')) AS ORG_CODE FROM SGT_ORG_PLAN PL GROUP BY PL.ORG_ID
I was looking for simple answers on the web I came across this link, that helped me to get it done in a simple SQL statement. Recording it here for future reference. This technique is called Blackbox XML.
No related posts.
Tags: mysql, row to column, transpose row