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 2005 Forums
 Transact-SQL (2005)
 Repeat records depending field value

Author  Topic 

makis_best
Starting Member

8 Posts

Posted - 2014-10-22 : 12:45:09
Hi all.

I have a table called Names like this

Number1 | Number2 | Number 3 | Name
==============================
Null | 2 | 3 | John
1 | Null | Null | Peter
Null | 3 | Null | Bill

And I want a result like this

1 | Number2 | John
2 | Number2 | John
3 | Number3 | John
4 | Number3 | John
5 | Number3 | John
6 | Number1 | Peter
7 | Number2 | Bill
8 | Number2 | Bill
9 | Number2 | Bill

How i can do that?

Please help.
Thank you.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-22 : 13:34:39
use UNPIVOT
Go to Top of Page

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2014-10-22 : 17:41:59
Try something like this:

WITH Repeater(number) AS
(
SELECT 1 UNION ALL
SELECT 2 UNION ALL SELECT 2 UNION ALL
SELECT 3 UNION ALL SELECT 3 UNION ALL SELECT 3
)
, CTE(tag, name) AS
(
SELECT 'Number1', N.Name
FROM Names AS N
INNER JOIN
Repeater AS R
ON R.number = N.Number1
UNION ALL
SELECT 'Number2', N.Name
FROM Names AS N
INNER JOIN
Repeater AS R
ON R.number = N.Number2
UNION ALL
SELECT 'Number3', N.Name
FROM Names AS N
INNER JOIN
Repeater AS R
ON R.number = N.Number3
)
SELECT ROW_NUMBER() OVER(ORDER BY T.name) AS rownum, T.tag, T.name
FROM CTE AS T;



For us, there is only the trying. The rest is not our business. ~T.S. Eliot

Muhammad Al Pasha
Go to Top of Page

makis_best
Starting Member

8 Posts

Posted - 2014-10-24 : 03:18:49
malpashaa thank you for the reply.

Cuz i am new to SQL this code look little hard for me.
Can you help me little explaining what you doing here.

Thank you.
Go to Top of Page
   

- Advertisement -