• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 196
  • Last Modified:

Stored procedure to retreive data in MS Sql Server

i have a table in below mentioned format:

StateID   ColorID     ColorDesc
21           5             Green
21           18           Red
21            11          A
22           5          Green
22         19          B
23        55          M
27       100       Cofee
27       99        Blue

I want to stored procedure in MS Sql Server 2005  which should return the output in below format:
sateid   RequiredColorDetails
21         5|Green|18|Red|11|A
22       5|Green|19|B
23      55|M
27      100|Cofee|99|Blue

Please help me.
2 Solutions
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you need the "group_concat" emulation in sql server, with the XML trick:
Aneesh RetnakaranDatabase AdministratorCommented:
SELECT DISTINCT StateID, (SELECT CAST(ColorID as varchar)+'|' +ColorDesc+'|' FROM urTable n WHERE a.stateid = n.stateid FOR XML PATH('') )
from urTable a
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now