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

T-SQL Translate data

Hi,  How can I translate data from a table to show up in a query in this manner:
Data  in table is like this:
Ed, 3
Ed, 2
Ed, 1
Ed, 4
Bob, 3
Bob, 2
Bob, 1
Bob, 4

output query to look like this:

Ed, 1, 2, 3, 5
Bob, 1, 2, 3, 4

Any help much appreciated.

Thanks.
0
sublimation
Asked:
sublimation
  • 2
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
please see this article for the implementation of a function to do that (the 1,2,3,4 going into 1 column!)
http://anthony-yio.blogspot.com/2007/12/mssql-groupconcat.html

the alternative (for sql 2000, for example) would be a dedicated function:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=33574
0
 
sublimationAuthor Commented:
Hi,

The 1,2,3,4 goes into 4 separate columns.  The example reference is not very clear.  was hoping for some sql code/pseudo code.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you will have to specify more, especially as you want several columns output.
please clarify a bit more about the input/output.
0
 
sublimationAuthor Commented:
Hi, Angel.

I have found a solution now thanks.  The column names do not matter, you can see a clear example of translation.  We can call the columns col1, col2, col3, just as long as the data is translated in the manner shown.

The way to do it was to create a join on the same table numerous times (for each column required).

Thanks for your help
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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