August 27, 2009 at 12:15 pm
Hi,
Is there a way to select records in this fashion?
/*
Table1:
col1 col2
aa 12
bb 22
cc 33
Table2:
col1 col2
zz 99
yy 88
xx 77
ww 66
vv 55
uu 44
Output:
COL
aa
zz
bb
yy
cc
xx
ww
vv
uu
if any of the table has more records it should be printed simply without any blanks*/
Do I need to use two cursors(or while loops).
Is there a way to achieve this in a single query?
Thanks,
KB
Thanks,
Santhosh
August 27, 2009 at 12:35 pm
Here is a quick shot. You might have to adjust the order by in the row_number() fuctions to get your desired output.
CREATE TABLE #temp (test CHAR(2), test2 INT)
CREATE TABLE #temp2 (test CHAR(2), test2 INT)
INSERT INTO #temp
SELECT 'aa',1
UNION ALL
SELECT 'bb',2
UNION ALL
SELECT 'cc',3
INSERT INTO #temp2
SELECT 'zz',1
UNION ALL
SELECT 'yy',2
UNION ALL
SELECT 'xx',3
;WITH CTE AS
(
SELECT *, ROW_NUMBER() OVER (ORDER BY test) AS rowNum FROM #temp
UNION ALL
SELECT *, ROW_NUMBER() OVER (ORDER BY test) AS rowNum FROM #temp2
)
SELECT * FROM CTE
ORDER BY rowNum
DROP TABLE #temp, #temp2
August 27, 2009 at 1:08 pm
I would modify your code to add a table number to guarantee that they are interleaved.
WITH CTE AS
(
SELECT *, 1 AS tableNum, ROW_NUMBER() OVER (ORDER BY test) AS rowNum FROM #temp
UNION ALL
SELECT *, 2 AS tableNum, ROW_NUMBER() OVER (ORDER BY test) AS rowNum FROM #temp2
)
SELECT * FROM CTE
ORDER BY rowNum, tableNum
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 27, 2009 at 2:36 pm
And w/o the CTE:
DECLARE @Table1 TABLE (col1 char(2), col2 int)
INSERT INTO @Table1
SELECT 'aa', 12 UNION ALL
SELECT 'bb', 22 UNION ALL
SELECT 'cc', 33
DECLARE @Table2 TABLE (col1 char(2), col2 int)
INSERT INTO @Table2
SELECT 'zz', 99 UNION ALL
SELECT 'yy', 88 UNION ALL
SELECT 'xx', 77 UNION ALL
SELECT 'ww', 66 UNION ALL
SELECT 'vv', 55 UNION ALL
SELECT 'uu', 44
SELECT Col1,
Col2
FROM (
SELECT ROW_NUMBER() OVER(ORDER BY Col1) as RowNumber,
COl1,
COl2
FROM @Table1 t1
UNION ALL
SELECT ROW_NUMBER() OVER(ORDER BY Col1 DESC),
COl1,
COl2
FROM @Table2 t2
) t
ORDER BY RowNumber, COl1
August 27, 2009 at 3:42 pm
John Rowan (8/27/2009)
And w/o the CTE:
But that solution is essentially the same as the CTE and the CTE is so much easier to read. What do you gain by not using the CTE?
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 27, 2009 at 3:51 pm
I did not say to not use a CTE. Its just an alternative way of coding the same solution. As far as ease-of-reading goes, they're almost identical w/ a little formatting:
SELECT Col1, Col2 FROM
(
SELECT ROW_NUMBER() OVER(ORDER BY Col1) as RowNumber, * FROM @Table1 t1
UNION ALL
SELECT ROW_NUMBER() OVER(ORDER BY Col1 DESC), * FROM @Table2 t2
) t
ORDER BY RowNumber, COl1
August 27, 2009 at 5:00 pm
John Rowan (8/27/2009)
I did not say to not use a CTE. Its just an alternative way of coding the same solution. As far as ease-of-reading goes, they're almost identical w/ a little formatting:
Only because the solution is so short. As the solution becomes more complex, the CTE becomes much simpler to read, because it better reflects how the mind processes the query.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 18, 2009 at 7:29 am
Followup question on this type of problem -
if we make the assumption that table1 and table2 have the exact same data, but we know that without an ORDER BY clause data is not guaranteed to be retrieved in the same manner, does the ORDER BY in this case only apply to the final resultset? Or does it apply to each piece (retrieval from table1 ORDER BY foo and also retrieval from table2 ORDER BY foo)?
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply