June 15, 2011 at 1:04 pm
Need to bring in the same table field into different columns in the same row of the sql query for each different value instead of different rows: example 1 is how I have it, but need it to be in the 2nd format. Don't know sql well at all. the examples below are just a snippet of the info.
Inc # Category Date openedlabelValue
1cat a1/1/2001 client ABC
1cat a1/1/2001 system 123
Inc #CategoryDate openedClientSystem
1cat a1/1/2001 ABC 123
Table is prp and the fields are Label and Value, both of which can have multiple entries. Hope this makes sense.
Thank you in advance!
Karen
June 15, 2011 at 1:15 pm
Why can't you do it in a matrix?
June 15, 2011 at 1:16 pm
If i understand the question then I think the easies solution is to use the for XML
Something along the lines of
Select [Inc#], [Category], [Date], [opened]
[System] = STUFF((Select ', ' + TI.System From [Tablename] TI Where TI.Inc = TO.Inc FOR XML PATH('')) ,1,2,'')
From [Tablename] TO
This should concatenate your ABC and 123 into one column(comma separated).
June 15, 2011 at 1:18 pm
He needs to pivot them into columns, not concatenate them into a single string.
June 15, 2011 at 2:38 pm
Unfortunately, I have not worked with XML. I know "some" sql, but this is not something I have tried to tackle before.
Thank you for your reply.
June 15, 2011 at 2:48 pm
Karren Turner (6/15/2011)
Unfortunately, I have not worked with XML. I know "some" sql, but this is not something I have tried to tackle before.Thank you for your reply.
#1 I'm not sure this works in sql 2000, pretty sure it doesn't but I can't test.
#2 It doesn't solve your problem anyways.
What you need is to build a matrix and there are plenty of samples of that on google.
June 15, 2011 at 7:19 pm
for some reason, i haven't used the matrix reporting much. As soon as I figure out how to get all of the column names added, I think this will worik!
Thank you!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply