Caolumn values to be displayed horizantally

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

SQL Server 2000 Forums

SQL Server Development (2000)

Caolumn values to be displayed horizantally

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-02-23 : 08:16:51

Suresh writes "Hi friends,

Table
Id value
-- -----
1 a
1 b
1 c
2 d

I need a query to print out value as if I pass id as 1

field1
------
a,b,c

Using only query not SP.

Thanks in advance"

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-02-23 : 12:20:34

Here's what I've done....yeah, this contains a CURSOR

. Plug this into your SQA and you'll see in the messages how this is building. You can determine from there what you want to do with the results.

I hope this helps:

SET NOCOUNT ON

DROP TABLE [Whatever]

CREATE TABLE Whatever (ID int NULL, Value varchar(5) NULL)

INSERT INTO Whatever VALUES(1, 'a')
INSERT INTO Whatever VALUES(1, 'b')
INSERT INTO Whatever VALUES(1, 'c')
INSERT INTO Whatever VALUES(2, 'a')
GO
SELECT ID, Value FROM Whatever
GO
declare @Id int
declare @ctr int
declare @value varchar(5)
declare @PrevId int
declare @PrevValue varchar(5)
declare @RowNum int
declare @holder varchar(8000)
declare List cursor for
select ID, Value from Whatever
OPEN List
FETCH NEXT FROM List
INTO @Id, @value
set @RowNum = 0
set @ctr = 0
set @holder = ' '
set @PrevID = 0
set @PrevValue = ' '
WHILE @@FETCH_STATUS = 0
BEGIN
set @RowNum = @RowNum + 1
IF @PrevID <> @Id
BEGIN
set @ctr = @ctr + 1
set @holder = @Value
set @PrevId = @Id
print ' IF =>'+cast(@ctr as char(5))
print ' IF '+cast(@RowNum as char(1)) + ' ID=>' + cast(@Id as char(1))
print ' IF '+cast(@RowNum as char(1)) + ' Value=>' + @Value + ' Holder=>' + @holder
END
ELSE
BEGIN
set @ctr = @ctr + 1

set @PrevValue = @Value
set @holder = ltrim(@holder)+' '+@Value
print ' ELSE =>'+cast(@ctr as char(5))
print ' ELSE '+cast(@RowNum as char(1)) + ' ID=>' + cast(@PrevId as char(1))
print ' ELSE '+cast(@RowNum as char(1)) + ' Value=>' + @Value + ' Holder=>' + @holder
END
--
FETCH NEXT FROM List
INTO @Id, @value

print ' OUTSIDE =>'+cast(@ctr as char(5))
print cast(@RowNum as char(1)) + ' ' + cast(@Id as char(1))+ ' value=>'+@value+' holder=>' +@holder
END
CLOSE List
DEALLOCATE List

Semper fi, Xerxes, USMC(Ret.)
--------------------------------------------------
Once a Marine ALWAYS a Marine!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-23 : 12:34:56

no no no...
declare @id int
set @id = 1

Declare @List varchar(1000)
SELECT @List = COALESCE(@List + ', ', '') + value
FROM MyTable
WHERE id = @id
SELECT @List

Go with the flow & have fun! Else fight the flow

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-02-23 : 12:40:22

quote:
Originally posted by spirit1

no no no...
declare @id int
set @id = 1

Declare @List varchar(1000)
SELECT @List = COALESCE(@List + ', ', '') + value
FROM MyTable
WHERE id = @id
SELECT @List

Go with the flow & have fun! Else fight the flow

But Mladen, it only performs the task on the first field (1), you NEVER get to 2

! So...you can't do this without a (gulp!) CURSOR

Semper fi, Xerxes, USMC(Ret.)
--------------------------------------------------
Once a Marine ALWAYS a Marine!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-23 : 13:00:30

this is a good thing to put in a UDF

you pass in the id paramter and it returns the @list.

Go with the flow & have fun! Else fight the flow

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-02-23 : 13:05:49

YEP! You're right

! A UDF is perfect for that

. Don't know why I was thinking CURSOR

!

Semper fi, Xerxes, USMC(Ret.)
--------------------------------------------------
Once a Marine ALWAYS a Marine!

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-02-23 : 13:34:28

A way to do this without a udf:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=46295

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-02-23 : 13:35:38

quote:
Originally posted by Seventhnight

A way to do this without a udf:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=46295

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

C-L-E-V-E-R

Semper fi,

Xerxes, USMC(Ret.)
------------------------------------------------------
Once a Marine ALWAYS a Marine!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources