Retrieve in the same order of insertion

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Retrieve in the same order of insertion

Author

Topic

raky
Aged Yak Warrior

767 Posts

Posted - 2008-12-09 : 11:35:54

hi,

Please give me a query for below specification

DECLARE @T table ( A int, B int, C int , S int)
insert into @t
select 3926, 3301, 6212, 3487 union all
select 3926, 3301, 6226, 3487 union all
select 3926, 3302, 6222, 3507 union all
select 3926, 3302, 6227, 3507 union all
select 3926, 3302, 6228, 3507 union all
select 3927, 3303, 6221, 3507 union all
select 3927, 3303, 6225, 3507 union all
select 3927, 3304, 6220, 3509 union all
select 3927, 3304, 6223, 3509 union all
select 3926, 3306, 6229, 3468

select * from @t

required o/p is

A sids
3926 3487,3507,3468
3927 3507,3509

I should get sids concatenated in the order in which 'S' is inserted for 'A'

Thanks.....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-09 : 11:40:28

[code]SELECT t.A,LEFT(l.lst,LEN(l.lst)-1) AS vallist
FROM (SELECT DISTINCT A FROM @T)t
CROSS APPLY (SELECT DISTINCT CAST(S AS varchar(10)) + ','
FROM @T
WHERE A=t.A
ORDER BY B
FOR XML PATH(''))l(lst)
[/code]

raky
Aged Yak Warrior

767 Posts

Posted - 2008-12-09 : 11:43:16

sorry iam getting errormessage as below

Msg 145, Level 15, State 1, Line 16
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-09 : 11:50:17

[code]
;With CTE (A, B, C , S,Seq)AS
(
SELECT A,B,C,S,ROW_NUMBER() OVER (PARTITION BY A,S ORDER BY C)
FROM @T
)
SELECT t.A,LEFT(l.lst,LEN(l.lst)-1) AS vallist
FROM (SELECT DISTINCT A FROM CTE)t
CROSS APPLY (SELECT CAST(S AS varchar(10)) + ','
FROM CTE
WHERE A=t.A
AND Seq=1
ORDER BY B
FOR XML PATH(''))l(lst)[/code]

raky
Aged Yak Warrior

767 Posts

Posted - 2008-12-09 : 11:55:47

Thank you very much visakh...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-09 : 11:59:19

You're welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources