Solved

Stored procedure to retreive data in MS Sql Server

Posted on 2009-07-05
2
183 Views
Last Modified: 2012-05-07
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
etc..


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
etc..

Please help me.
0
Comment
Question by:ram27
2 Comments
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 200 total points
ID: 24780960
you need the "group_concat" emulation in sql server, with the XML trick:
http://blog.shlomoid.com/2008/11/emulating-mysqls-groupconcat-function.html
0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 300 total points
ID: 24781069
SELECT DISTINCT StateID, (SELECT CAST(ColorID as varchar)+'|' +ColorDesc+'|' FROM urTable n WHERE a.stateid = n.stateid FOR XML PATH('') )
from urTable a
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now