Raasukutty's

Tag: transpose

  • Converting Rows to Columns in SQL

    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.