help plz

Dear All,

Greetings!!

I've a table like this:

No Desc
-- ----
1 one1
1 one2
1 one3
2 two1
2 two2
2 two3
3 three1
3 three2
3 three3
3 three4
The no. repeats maximum 4 times(ie. 1 is there in above table 3 times and 3 is repeated 4 times and so on.)

Now I want to display the above records like below using SQL query:

No desc1 desc2 desc3 desc4
------------------------------
1 one1 one2 one3
2 two1 two2 two3
3 three1 three2 threee3 three4

Can anyone help me out please??

regards,

Rajendar


ok

Hi ya,

This is really a client side operation...

you may be able to do it using a function and a cursor within that function and then do

select distinct no, fn_get_desc( no ) from table

But if possible I'd do this on the client machine by ordering the result set and looping through it

Cheers
Twan

It may be ok if you have a sequence number for each description as in:


No Seq Desc
-- --- ----
1 1 one1
1 2 one2
1 3 one3
2 1 two1
2 2 two2
2 3 two3
3 1 three1
3 2 three2
3 3 three3
3 4 three4


select
No,
max( case Seq when 1 then Desc else NULL end ) as desc1,
max( case Seq when 2 then Desc else NULL end ) as desc2,
max( case Seq when 3 then Desc else NULL end ) as desc3,
max( case Seq when 4 then Desc else NULL end ) as desc4
from
table
group by
No

I generated this a while ago for someone else.......



declare @tbl_Test TABLE (ID INT, Text VARCHAR(10))
declare @tbl_New TABLE (ID INT, Text VARCHAR(1000))
declare @cItemList varchar(8000)

INSERT INTO @tbl_Test VALUES(1,'Text 1')
INSERT INTO @tbl_Test VALUES(1,'Text 2')
INSERT INTO @tbl_Test VALUES(1,'Text 3')
INSERT INTO @tbl_Test VALUES(2,'Text 1')
INSERT INTO @tbl_Test VALUES(2,'Text 2')
INSERT INTO @tbl_Test VALUES(3,'Text 1')
INSERT INTO @tbl_Test VALUES(3,'Text 2')
INSERT INTO @tbl_Test VALUES(3,'Text 3')
INSERT INTO @tbl_Test VALUES(3,'Text 4')
INSERT INTO @tbl_Test VALUES(4,'Text 1')

DECLARE @Min INT, @Max INT
select @Min = min(ID) from @tbl_Test
select @Max = max(ID) from @tbl_Test
while @Min <= @Max
begin
SELECT @cItemList = COALESCE(@cItemList + ',' , '') + CAST(Text AS varchar(10))
FROM @tbl_Test
where ID = @Min

INSERT INTO @tbl_New
VALUES (@Min,@cItemList)
SET @Min = @Min + 1
SET @cItemList = NULL

end
SELECT * from @tbl_New

SQL Developer
JustEnough Software Corporation
Walking on water and coding of a spec is easy, as long as both are frozen - A friend

yep that would work too, although

NOTE that that is an undocumented feature, and is not ANSI standard. It is likely to break in future releases of SQLServer...

Cheers
Twan

Twan,

Is COALESCE undocumented? I found it in BOL.

Sanette

SQL Developer
JustEnough Software Corporation
Walking on water and coding of a spec is easy, as long as both are frozen - A friend

Hi Sanette,

coalesce is documented, but the concatenation of column values into a variable as the statement traverses rows is.

The normal behavious would be that the variable takes on the value of each row independently, so in your example it would result in being the last Text in your result set

Cheers
Twan