December 27, 2010 at 7:34 am
How to convert the columns to row. Below is an eg.
need to get the list of names for a group in a comma separated.
I have a table
id name
-----------
1 test
2 again
1 work
2 better
i want the result should be
result:
id list(name)
-----------------
1 test ,work
2 again ,better
December 27, 2010 at 8:35 am
Is the "ID" column from another table that lists the valid values? If so, that makes the query a little bit simpler.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 27, 2010 at 8:42 am
One way to get related items into a delimited column is using a sub-select and for xml clause. This involves the awkward problem of stripping off the leading or trailing seperator, and in this example I'm solving that by using the substring function to return all but the first character.
select
m.productmodelid,
substring(
(
select ', ' + name
from adventureworks.production.product as p
where p.productmodelid = m.productmodelid for xml path('')
)
,3,8000) as product_name
from adventureworks.production.productmodel m
where m.productmodelid in (1,2,3);
productmodelid product_name
-------------- ------------
1 Classic Vest, S, Classic Vest, M, Classic Vest, L
2 AWC Logo Cap
3 Full-Finger Gloves, S, Full-Finger Gloves, M, Full-Finger Gloves, L
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
December 28, 2010 at 12:28 am
How about something like this based on your sample data:
declare @testtab table (
id int,
name varchar(10)
);
insert into @testtab
values (1, 'test'),
(2, 'again'),
(1, 'work'),
(2, 'better');
select
id,
name
from
@testtab;
with UniqueIds as (
select distinct
id
from
@testtab
)
select
ui.id,
stuff((select ', ' + tt.name from @testtab tt where tt.id = ui.id for xml path('')),1,2,'') as ListName
from
UniqueIds ui
;
The code above is SQL Server 2008 since this is the forum posted.
December 28, 2010 at 3:34 am
Is it possible to retrieve it in a single query? Please note i'm having dynamic values in the record... but fixed ten columns only
I've just tried to create xml and using xquery i have converted it to columns from rows. But i couldnt able to join table valued function..
Could you possibly let me know whether we can able to achieve it in some scenario?
December 28, 2010 at 7:02 am
vinothraj (12/28/2010)
Is it possible to retrieve it in a single query? Please note i'm having dynamic values in the record... but fixed ten columns onlyI've just tried to create xml and using xquery i have converted it to columns from rows. But i couldnt able to join table valued function..
Could you possibly let me know whether we can able to achieve it in some scenario?
Not sure what you are talking about. The problem you are relating does not appear related to the OPs original question. I suggest posting in a new thread providing as much detail as possible including DDL for tables, sample data, expected results based on sample data, and what you have tried so far to solve your problem.
December 29, 2010 at 2:08 am
Lynn Pettis (12/28/2010)
Not sure what you are talking about. The problem you are relating does not appear related to the OPs original question. I suggest posting in a new thread providing as much detail as possible including DDL for tables, sample data, expected results based on sample data, and what you have tried so far to solve your problem.
Ok, Just let me know whether its possible to generate the required records in a single query? Please take the original posting as given input.
December 29, 2010 at 2:37 am
vinothraj (12/29/2010)
Lynn Pettis (12/28/2010)
Not sure what you are talking about. The problem you are relating does not appear related to the OPs original question. I suggest posting in a new thread providing as much detail as possible including DDL for tables, sample data, expected results based on sample data, and what you have tried so far to solve your problem.
Ok, Just let me know whether its possible to generate the required records in a single query? Please take the original posting as given input.
Please post a sample (based on the given input in the first post) whre Lynns solution would fail.
I agree with Lynn: you're dealing with a different scenario. So you should start a new thread providing the information already requested.
As a side note: taking the table setup aside, Lynns solution IS a single query....
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply