Monday, June 24, 2013

Comma separated value in sql query




One of my projects I want to get city postal code as a one column with comma separated. I want two columns as an output. One is State and another one is commas separated postal code in the state.
The below query will help you to get the each state with comma separated postal code.

Code:

SELECT State,
      Post_Code =
        STUFF((SELECT ', ' + Post_Code
           FROM your_table b
           WHERE b. State = a. State
          FOR XML PATH('')), 1, 2, '')
FROM your_table a
GROUP BY State


OUTCOME:
State
Postal_Code
Tamilnadu
600001,600256,638656,638657
Kerala
678965,670059,670056,673598

No comments: