Listing multiple values for one record

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

Listing multiple values for one record

Author

Topic

marino3d
Starting Member

18 Posts

Posted - 2011-08-17 : 19:14:36

Kristen
Test

22859 Posts

Posted - 2011-08-17 : 19:26:28

If your EMAIL table had a column for ItemNo (from 1 to 5) you could do:


SELECT IDNumber, FName, Lastname,
       [Email1] = E1.EmailAddress,
       [Email2] = E2.EmailAddress,
        ...
FROM [name] AS N
     LEFT OUTER JOIN [email] AS E1
         ON E1.IDNumber = N.IDNumber
        AND E1.ItemNo = 1
     LEFT OUTER JOIN [email] AS E2
         ON E2.IDNumber = N.IDNumber
        AND E2.ItemNo = 2
     ...

Kristen
Test

22859 Posts

Posted - 2011-08-17 : 20:09:47

"Does SQL have anything like that where you can sort a table and traverse it up and down kinda like an array?"

You could use ROW_NUMBER() OVER. That could get you the Nth row, based on the Creation date value - and would thus be repeatable. Bit messy to have to do that 5 times though ... (I posted a similar code sample on another thread, in case it helps: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=164396#640978)

You might be able to use PIVOT too

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2011-08-17 : 20:40:40

Using row_number() and case, then join the 2 tables, you can solve this problem. If need more help in detail, let me know.

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2011-08-18 : 00:39:05

No .... run the test below to see if it works for you.

declare @temp1 table(idNumber int, fn varchar(10), ln varchar(10))
insert into @temp1 values(1,'a','b')
insert into @temp1 values(3,'c','d')
insert into @temp1 values(2,'e','f')

declare @temp2 table(idNumber int, email varchar(100))
insert into @temp2 values(1,'1email1')
insert into @temp2 values(2,'2email2')
insert into @temp2 values(2,'2email3')
insert into @temp2 values(1,'1email2')
insert into @temp2 values(1,'1email3')
insert into @temp2 values(3,'3email2')
insert into @temp2 values(3,'3email1')
insert into @temp2 values(1,'1email4')
insert into @temp2 values(2,'2email1')
insert into @temp2 values(1,'1email5')

;with temp as
(
select idNumber,
max(case when rn = 1 then email else null end) as e1
,max(case when rn = 2 then email else null end) as e2
,max(case when rn = 3 then email else null end) as e3
,max(case when rn = 4 then email else null end) as e4
,max(case when rn = 5 then email else null end) as e5
from (select idNumber, email, rn = ROW_NUMBER() over(partition by idNumber order by email) from @temp2)t group by idNumber
)
select te.idNumber, te.fn, te.ln, t.e1,t.e2,t.e3,t.e4,t.e5 from @temp1 te join temp t on t.idNumber=te.idNumber

--- test result
1 a b 1email1 1email2 1email3 1email4 1email5
2 e f 2email1 2email2 2email3 NULL NULL
3 c d 3email1 3email2 NULL NULL NULL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources