COALESCE? Create list field from table

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

COALESCE? Create list field from table

Author

Topic

valedaw2
Yak Posting Veteran

50 Posts

Posted - 2008-11-26 : 13:08:21

I have TableA with field1, field2, field3.

I need to create a field in TableB that will list all of the DISTINCT values for field3, that have the same field1, field2 combination from TableA.

Is this possible with COALESCE?

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-11-26 : 13:10:43

how do you want the distinct values to show up in tableB? As one record, like 'a,b,c,...' or in different rows.

valedaw2
Yak Posting Veteran

50 Posts

Posted - 2008-11-26 : 13:13:26

Actually, I need to populate a field within a record for each combination of field1, field2.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-11-26 : 13:19:22

What if there are more than 1 field3 values for combination of field1 and field2? If there isn't, You query should be like

Update b 
set b.newfield=a.field3
from tableB b join tableA a on a.field1=b.field1 and a.field2=b.field2

valedaw2
Yak Posting Veteran

50 Posts

Posted - 2008-11-26 : 13:33:16

There can be several values for field3. I for b.newfield to contain the list of distinct values that TableA contains for field3.

Example:
TableA field1 field2 field3
1 2 555
1 2 342
1 2 536
1 2 555
1 2 555
1 3 111
1 3 777
1 4 222
1 4 222

So TableB should look like
Field1 Field2 Newfield
1 2 '555, 342, 536'
1 3 '111, 777'
1 4 '222'

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-11-26 : 15:16:22

SELECT t.field1,t.field2,LEFT(sl.numlist,LEN(sl.numlist)-1) as newfield
FROM(SELECT DISTINCT field2,field1 FROM table) t
CROSS APPLY(SELECT CAST(field3 AS varchar(10)) + ',' AS [text()]
FROM table
WHERE field2= t.field2
FOR XML PATH(''))sl(numlist)

valedaw2
Yak Posting Veteran

50 Posts

Posted - 2008-11-26 : 15:20:18

I am working in SQL 2005. This is actually a query in SQL Server Management Studio.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-11-26 : 15:25:07

Try this in SQL 2005.

valedaw2
Yak Posting Veteran

50 Posts

Posted - 2008-11-26 : 15:31:54

It appears that you are only using one table

valedaw2
Yak Posting Veteran

50 Posts

Posted - 2008-11-26 : 15:35:45

I got it to give me the list, but not distinct values. Doesn't the distinct need to be where you have the CROSS APPLY?

valedaw2
Yak Posting Veteran

50 Posts

Posted - 2008-11-26 : 15:37:09

That was it. YEY!!!! Thanks so much! You made my Thanksgiving holiday brighter.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-11-26 : 15:42:49

quote:
Originally posted by valedaw2

That was it. YEY!!!! Thanks so much! You made my Thanksgiving holiday brighter.

Yep you are right!!

SELECT t.field1,t.field2,LEFT(sl.numlist,LEN(sl.numlist)-1) as newfield
FROM(SELECT DISTINCT field2,field1 FROM table) t
CROSS APPLY(SELECT distinct CAST(field3 AS varchar(10)) + ',' AS [text()]
FROM table
WHERE field2= t.field2
FOR XML PATH(''))sl(numlist)

valedaw2
Yak Posting Veteran

50 Posts

Posted - 2008-11-26 : 16:31:01

This is what I ended up doing. Thanks to your expertise:

SELECT t.field1,t.field2,LEFT(sl.numlist,LEN(sl.numlist)-1) as newfield
into #newfield
FROM(SELECT DISTINCT field2,field1 FROM tableB) t
CROSS APPLY(SELECT distinct CAST(field3 AS varchar(10)) + ',' AS [text()]
FROM tableA
WHERE field2= t.field2
FOR XML PATH(''))sl(numlist)

Then I did a join on the TableA and #newfield to populate the field.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2008-11-26 : 23:40:05

SELECT DISTINCT
field1,
field2,
(STUFF((SELECT DISTINCT ',' + CAST(field3 AS VARCHAR(255))
FROM tablea WHERE field2= t.field2
FOR XML PATH('')), 1, 1, ''))
FROM tablea AS t

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2008-11-27 : 00:06:26

SELECT field1,field2,
STUFF((SELECT DISTINCT ',' + CONVERT(VARCHAR(22),field3) FROM tbl WHERE field1 = t.field1 and field2 = t.field2 FOR XML PATH('')),1,1,'')
FROM tbl t
GROUP BY field1,field2

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources