need help with tsql

[eirikr]

Having 4 tables

System has columns: id, sysName
PC has has columns: id, serialNum
Mac has has columns: id, macAddr
IP has has columns: id, ipAddr

Example:

System Serial Mac IP

Sys1 Ser1 Mac1 IP1
Sys1 Ser1 Mac2 IP2


How can I make the output like

Sys1 Ser1 “Mac1; Mac2” “IP1; IP2”

Please help (using T_sql, MSSQL 2005)
Thanks

 

[pbaldy]

See if this works for you:

http://www.dbforums.com/showthread.php?t=1605725

Paul
MS Access MVP 2007/2008

 

[gmmastros]

pbaldy,

The solution you linked to is an acceptable method for handling this type of problem. Unfortunately, there is a small problem with the code posted. There should be a filter in the query that removes NULLs. If there is a NULL in the middle of the data, the comma separated list will not be accurate. Depending on the method used, it could return NULL, or an incomplete list.

Here's an example to demonstrate the problem:

Declare @Temp Table(Id Int, Value VarChar(20))

Insert Into @Temp Values(1, 'A')
Insert Into @Temp Values(2, 'B')
Insert Into @Temp Values(3, NULL)
Insert Into @Temp Values(4, 'D')
Insert Into @Temp Values(5, 'E')

Declare @CommaList VarChar(8000)
Set @CommaList = ''

Select @CommaList = @CommaList + ',' + Value
From   @Temp
Order By Id

Select @CommaList

This code outputs NULL as the result.

This problem is easy enough to fix. Just put an IsNull/Coalesce test on the value.

Declare @Temp Table(Id Int, Value VarChar(20))

Insert Into @Temp Values(1, 'A')
Insert Into @Temp Values(2, 'B')
Insert Into @Temp Values(3, NULL)
Insert Into @Temp Values(4, 'D')
Insert Into @Temp Values(5, 'E')

Declare @CommaList VarChar(8000)
Set @CommaList = ''

Select @CommaList = @CommaList + ',' + [!]Coalesce([/!]Value[!], '')[/!]
From   @Temp
Order By Id

Select @CommaList

This time, you get: [tt][blue],A,B,,D,E[/blue][/tt]

This is better, but still not quite right. The double comma's exist where the value from the original table is NULL. Instead of using Coalesce, it's better to put the condition in the WHERE clause, like this...

Declare @Temp Table(Id Int, Value VarChar(20))

Insert Into @Temp Values(1, 'A')
Insert Into @Temp Values(2, 'B')
Insert Into @Temp Values(3, NULL)
Insert Into @Temp Values(4, 'D')
Insert Into @Temp Values(5, 'E')

Declare @CommaList VarChar(8000)
Set @CommaList = ''

Select @CommaList = @CommaList + ',' + Value
From   @Temp
[!]Where  Value Is NOT NULL[/!]
Order By Id

Select @CommaList

This time, the output is: [tt][blue],A,B,D,E[/blue][/tt]

We still have a potential problem though. What if the original table had an empty string instead of null? We would get the double comma problem again.

So, let's modify the where clause to accommodate empty strings.

Declare @Temp Table(Id Int, Value VarChar(20))

Insert Into @Temp Values(1, 'A')
Insert Into @Temp Values(2, 'B')
Insert Into @Temp Values(3, [!]''[/!])
Insert Into @Temp Values(4, 'D')
Insert Into @Temp Values(5, 'E')

Declare @CommaList VarChar(8000)
Set @CommaList = ''

Select @CommaList = @CommaList + ',' + Value
From   @Temp
[!]Where  Value > ''[/!]
Order By Id

Select @CommaList

This will work better now. If the value is null, an empty string, or even a string of spaces, you will not get the double comma problem, but there are more improvements. You see, it's weird to have a comma at the beginning of the output. It would be equally weird to have it at the end. There are several ways to fix this problem. Usually, what I do is to remove the comma at the beginning of the string, like this...

Declare @Temp Table(Id Int, Value VarChar(20))

Insert Into @Temp Values(1, 'A')
Insert Into @Temp Values(2, 'B')
Insert Into @Temp Values(3, NULL)
Insert Into @Temp Values(4, 'D')
Insert Into @Temp Values(5, 'E')

Declare @CommaList VarChar(8000)
Set @CommaList = ''

Select @CommaList = @CommaList + ',' + Value
From   @Temp
Where  Value Is NOT NULL
Order By Id

[!]If Left(@CommaList, 1) = ','
    Set @CommaList = Right(@CommaList, Len(@CommaList)-1)
[/!]
Select @CommaList

Now, the output looks like this: [tt][blue]A,B,D,E[/blue][/tt]

This is the output we probably want. I hope you realize that with some minor modifications to the code, we can make it more robust and potentially faster (because the filter removes rows we don't care about).

Now, I will admit that the extra code I added may not be necessary based on the particular table(s) involved. However, if you are going to base future coding on this, it's best to base it on code that is more robust.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom