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.

Advertisements

2 thoughts on “Converting Rows to Columns in SQL

  1. Hi,

    I have a problem.
    I have a table having three columns

    AcctID AcctName AcctType AcctOpenYear
    1 AB Checking 2002
    2 BC Saving 2002
    3 CD Checking 2003
    4 DE Saving 2003
    5 DE Saving 2003

    Now the requirement is to show the results Grouped by AccType, and separated by year
    The desired output should be

    AcctType NoOfAccounts AcctsOpened2002 AcctsOpened2003
    Saving 3 1 2
    Checking 2 1 1

    Even the following output will also work
    AcctType AcctsOpened2002 AcctsOpened2003
    Saving 1 2
    Checking 1 1

    Any help in this regard is more than welcome.

    Thanks

  2. Hi,
    Ur query s good, but pls help me in the below condition
    JobDate TickerName
    ———- ————
    27-01-2011 MSFT
    13-01-2011 INFY
    13-01-2011 INFY
    28-10-2010 MSFT
    15-10-2010 INFY
    I wanted to transpose the above table as,

    TicketName JobDate1 JobDate2 JobDate3 JobDate4 JobDate5
    INFY 13-01-2011 13-01-2011 15-10-2010 15-10-2010 13-07-2010
    MSFT 27-01-2011 28-10-2010 22-07-2010 22-04-2010 21-04-2010

    Please reply to
    j_vijaydaniel@yahoo.com

    Thanks in advance,
    Vijay

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s