change layout of data - SQL Server Forums

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

Transact-SQL (2000)

change layout of data

Author

Topic

jamie
Aged Yak Warrior

542 Posts

Posted - 2004-06-25 : 10:11:59

Select id, description from table
give me data like

Id description
1 C
1 D
2 C
2 E

how can I put it like :

1 C D
2 C E

?thank you for the help.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-06-25 : 11:10:18

In the desired result set, is 'C D' in one column or is 'C' in one and 'D' in one?

Will there always be exactly two descriptions per Id, or will there be a variable number of descriptions per Id?

If one column, then you could write a UDF to that builds the list,

quote:

Create Function descriptionsToList
(
@id int
)
Returns nvarchar(100) As
Begin

Declare @rtnVal nvarchar(100)

Select @rtnVal = isnull(@rtnVal,'') + ' ' + description from myTable Where Id = @Id

Return @rtnVal
End
Go

EDIT: Then just call the function in your select:

quote:

Select A.id, Desciptions = dbo.descriptionsToList(A.id) From (Select Distinct Id From myTable) A

Corey

jamie
Aged Yak Warrior

542 Posts

Posted - 2004-06-30 : 05:01:03

there could a a random number of records.

therre could be a C,D and E or a C or a C and E or A D and E and so on.

is there a way this can be done through T-SQL, not using functions ?

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2004-06-30 : 05:04:39

You may want to look at this:-

http://www.sqlteam.com/item.asp?ItemID=2955

jamie
Aged Yak Warrior

542 Posts

Posted - 2004-06-30 : 05:32:52

thanks for the link,
on one of the sites I can now do something like :

selectID,
min(case when [Description] = 'C' then [Description] end) as C,
min(case when [Description] = 'D' then [Description] end) as D,
min(case when [Description] = 'E' then [Description] end) as E
from Disk
Group by ID

this works fine at present, but if more letters are added to the table I will need to modifiy this query.

jamie
Aged Yak Warrior

542 Posts

Posted - 2004-06-30 : 06:57:17

looking at that article, can I actually use this if I only have 2 fields ?
I have ID and Letter, I don't want to do any sums, just display the letters in one long line.
would this crosstab do the job ?

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-06-30 : 10:30:35

I still think the first function I mentioned is going to be the simplest thing

--aside from updating the query to add new letters

Corey

jamie
Aged Yak Warrior

542 Posts

Posted - 2004-07-01 : 08:29:51

thank you corey , I'll try it out.

jamie
Aged Yak Warrior

542 Posts

Posted - 2004-07-02 : 06:55:27

Corey, just to let you know I have finnally tested this and it is bloody brillient !!
thank you very much.