PIVOT and loop help - 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)

PIVOT and loop help

Author

Topic

rgombina
Constraint Violating Yak Guru

319 Posts

Posted - 2008-11-24 : 06:16:40

Hi again,

I would like to pivot the data and would like some help please...

Data:


ID        Col1        Col2
1         val1        newval1
1         val2        newval2
1         val3
1         val4

I would like to make multiple rows if number of rows reached. So here's what I am hoping to get:

ID        Col1        Col2
1         val1        newval1
1         val2        newval2
1         val3        newval1 <- repopulated
1         val4        newval2 <- repopulated

Basically, if Col1 reaches greater than 2 rows from the same ID, then new rows created with Col2 repopulated.

Thanks again...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-24 : 06:32:57

[code]DECLARE @Sample TABLE
(
ID INT,
col1 VARCHAR(20),
col2 VARCHAR(20)
)

INSERT @Sample
(
ID,
col1,
col2
)
SELECT 1, 'col1', 'newval1' UNION ALL
SELECT 1, 'col2', 'newval2' UNION ALL
SELECT 1, 'col3', NULL UNION ALL
SELECT 1, 'col4', NULL

SELECT *
FROM @Sample

DECLARE @Found TABLE
(
rowID INT IDENTITY(0, 1) PRIMARY KEY CLUSTERED,
col2 VARCHAR(20)
)

DECLARE @Items INT

INSERT @Found
(
col2
)
SELECT col2
FROM @Sample
WHERE col2 IS NOT NULL
ORDER BY col1

SET @Items = @@ROWCOUNT

DECLARE @Missing TABLE
(
rowID INT IDENTITY(0, 1) PRIMARY KEY CLUSTERED,
col1 VARCHAR(20),
col2 VARCHAR(20)
)

INSERT @Missing
(
col1,
col2
)
SELECT col1,
col2
FROM @Sample
WHERE col2 IS NULL
ORDER BY col1

UPDATE s
SET s.col2 = z.col2
FROM @Sample AS s
INNER JOIN (
SELECT m.col1,
f.col2
FROM @Found AS f
INNER JOIN @Missing AS m ON m.rowID % @Items = f.rowID
) AS z ON z.col1 = s.col1

SELECT *
FROM @Sample[/code]

E 12°55'05.63"
N 56°04'39.26"

rgombina
Constraint Violating Yak Guru

319 Posts

Posted - 2008-11-24 : 06:37:33

Wow Peso, thanks for a very quick reply! I really appreciate it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources