March 21, 2012 at 4:26 am
i have a table with the following data
r1
r2
r3
r4
r5
r6
r7
i wanted to display it in a grid with 3 columns
r1 r2 r3
r4 r5 r6
r7
i can do this with cursor (code below) but was wondering if there's a more elegant way of doing it
declare @t1 table(refno varchar(50))
insert into @t1
select 'r1' union
select 'r2' union
select 'r3' union
select 'r4' union
select 'r5' union
select 'r6' union
select 'r7'
declare @t2 table(rowId int, refno1 varchar(50), refno2 varchar(50), refno3 varchar(50))
declare @row int
declare @col int
set @row = 1
set @col = 1
declare @refno varchar(50)
declare cur CURSOR FOR
select refno from @t1
open cur
fetch next from cur
into @refno
while @@FETCH_STATUS = 0
begin
if @col = 1
insert into @t2 (rowId, refno1, refno2, refno3)
values (@row, @refno, null, null)
else if @col = 2
update @t2 set refno2 = @refno
where rowId = @row
else
begin
update @t2 set refno3 = @refno
where rowId = @row
set @row = @row + 1
set @col = 0
end
set @col = @col + 1
fetch next from cur
into @refno
end
close cur
deallocate cur
select refno1, refno2, refno3 from @t2
slow down when you need to hurry, stop when you need to move on,
look back when you need to forget, or you might slip and leave sanity
March 21, 2012 at 5:33 am
Here's one way: -
--Your sample data (Thanks, makes it much easier when readily consumable sample data exists)
DECLARE @t1 TABLE (refno VARCHAR(50));
INSERT INTO @t1
SELECT 'r1'
UNION SELECT 'r2'
UNION SELECT 'r3'
UNION SELECT 'r4'
UNION SELECT 'r5'
UNION SELECT 'r6'
UNION SELECT 'r7';
DECLARE @t2 TABLE (rowId INT, refno1 VARCHAR(50), refno2 VARCHAR(50), refno3 VARCHAR(50));
--Actual solution
INSERT INTO @t2
SELECT N,
NULLIF(MAX(CASE WHEN Y = 1 THEN refno ELSE '' END),'') AS refno1,
NULLIF(MAX(CASE WHEN Y = 2 THEN refno ELSE '' END),'') AS refno2,
NULLIF(MAX(CASE WHEN Y = 3 THEN refno ELSE '' END),'') AS refno3
FROM (SELECT refno, ROW_NUMBER() OVER(PARTITION BY N ORDER BY (SELECT NULL)), N
FROM (SELECT refno, (X/3)+1, X
FROM (SELECT refno,
ROW_NUMBER() OVER(ORDER BY (SELECT NULL))-1
FROM @t1) a(refno, X)
) b(refno, N, X)
) y(refno, Y, N)
GROUP BY N;
--Check results
SELECT * FROM @t2;
Produces: -
rowId refno1 refno2 refno3
----------- -------------------------------------------------- -------------------------------------------------- --------------------------------------------------
1 r1 r2 r3
2 r4 r5 r6
3 r7 NULL NULL
March 21, 2012 at 5:38 am
thanks Cadavre
slow down when you need to hurry, stop when you need to move on,
look back when you need to forget, or you might slip and leave sanity
March 21, 2012 at 6:05 am
Lambert Antonio (3/21/2012)
thanks Cadavre
No problem.
In case you're interested, the cursor is also massively outperformed by my method as well.
See: -
IF object_id('tempdb..#testEnvironment') IS NOT NULL
BEGIN
DROP TABLE #testEnvironment;
END;
--1,000,000 Random rows of data
SELECT TOP 1000000
'r' + CAST(ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS VARCHAR(7)) AS refno
INTO #testEnvironment
FROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2, master.dbo.syscolumns sc3;
IF object_id('tempdb..#testEnvironment2') IS NOT NULL
BEGIN
DROP TABLE #testEnvironment2;
END;
--Actual solution
SELECT N,
NULLIF(MAX(CASE WHEN Y = 1 THEN refno ELSE '' END),'') AS refno1,
NULLIF(MAX(CASE WHEN Y = 2 THEN refno ELSE '' END),'') AS refno2,
NULLIF(MAX(CASE WHEN Y = 3 THEN refno ELSE '' END),'') AS refno3
INTO #testEnvironment2
FROM (SELECT refno, ROW_NUMBER() OVER(PARTITION BY N ORDER BY (SELECT NULL)), N
FROM (SELECT refno, (X/3)+1, X
FROM (SELECT refno,
ROW_NUMBER() OVER(ORDER BY (SELECT NULL))-1
FROM #testEnvironment) a(refno, X)
) b(refno, N, X)
) y(refno, Y, N)
GROUP BY N;
--Check results
SELECT * FROM #testEnvironment2;
On my system, that takes around 6.3 seconds (half of which is the time taken to actually select the 333,334 results from the new table) to split 1 million rows into your 3 partitions.
If we do the same thing with your cursor: -
IF object_id('tempdb..#testEnvironment') IS NOT NULL
BEGIN
DROP TABLE #testEnvironment;
END;
--1,000,000 Random rows of data
SELECT TOP 1000000
'r' + CAST(ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS VARCHAR(7)) AS refno
INTO #testEnvironment
FROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2, master.dbo.syscolumns sc3;
IF object_id('tempdb..#testEnvironment2') IS NOT NULL
BEGIN
DROP TABLE #testEnvironment2;
END;
CREATE TABLE #testEnvironment2 (rowId int, refno1 varchar(50), refno2 varchar(50), refno3 varchar(50))
declare @row int
declare @col int
set @row = 1
set @col = 1
declare @refno varchar(50)
declare cur CURSOR FOR
select refno from #testEnvironment
open cur
fetch next from cur
into @refno
while @@FETCH_STATUS = 0
begin
if @col = 1
insert into #testEnvironment2 (rowId, refno1, refno2, refno3)
values (@row, @refno, null, null)
else if @col = 2
update #testEnvironment2 set refno2 = @refno
where rowId = @row
else
begin
update #testEnvironment2 set refno3 = @refno
where rowId = @row
set @row = @row + 1
set @col = 0
end
set @col = @col + 1
fetch next from cur
into @refno
end
close cur
deallocate cur
select * from #testEnvironment2
Well, I cancelled it after 2 minutes 30 seconds because I was bored of waiting 😛
March 23, 2012 at 11:40 am
Antonio,
Here is a solution that is a bit easier to read than Cadavre's solution. It doesn't perform quite as well on large record sets, however.
It's just another option.
CREATE TABLE #T
( RefNoVARCHAR(50))
GO
INSERT INTO #T
SELECT 'r1'
UNION SELECT 'r2'
UNION SELECT 'r3'
UNION SELECT 'r4'
UNION SELECT 'r5'
UNION SELECT 'r6'
UNION SELECT 'r7';
GO
WITH RefOrdered AS
( SELECT RefNo
, ROW_NUMBER() OVER (ORDER BY RefNo) AS RN
FROM #T
)
-- Get the column for the RefNo (1 - 3)
, ColOrd AS
( SELECT RefNo, RN
, CASE WHEN RN % 3 = 0 THEN 3 ELSE RN % 3 END AS ColNum
FROM RefOrdered
)
-- If you subtract the ColNum (1 - 3) from the absolute row number
-- you get distinct groups - i.e. 0, 3, 6. These act as an
-- anchor for the final output row
SELECT
MAX(CASE WHEN ColNum = 1 THEN RefNo ELSE '' END) AS RefNo1
, MAX(CASE WHEN ColNum = 2 THEN RefNo ELSE '' END) AS RefNo2
, MAX(CASE WHEN ColNum = 3 THEN RefNo ELSE '' END) AS RefNo3
FROM ColOrd
GROUP BY (RN - ColNum)
ORDER BY (RN - ColNum)
Todd Fifield
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply