September 13, 2013 at 11:11 pm
Hi. Here is my table initialization:
CREATE TABLE #table1(col nvarchar(1));
INSERT INTO #table1 SELECT 'A' UNION ALL SELECT 'B' UNION ALL SELECT 'C';
And here is my sql statement
WITH cte(col,Row_Num) as (
SELECT col, Row_Num= ROW_NUMBER() OVER (ORDER BY(SELECT 0)) FROM cte
UNION ALL
SELECT col, Row_Num= ROW_NUMBER() OVER (ORDER BY(SELECT 0)) FROM cte
)
SELECT * FROM CTE
This is output:
col,Row_Num
-------------
a,1
b,2
c,3
a,1
b,2
c,3
Can I change my output to this?
col,Row_Num
-------------
a,1
b,2
c,3
a,4
b,5
c,6
I already tried so many queries, I prevent to write them here to make you bored.
And please help me with a general query that solve all same problems, not only a query that solve this particular problem, because my real table are more complex.
Thank you very much for help.
___________________________________
Computer Enterprise Masoud Keshavarz
I don't care about hell.
If I go there I've played enough Diablo to know how to fight my way out.
September 13, 2013 at 11:38 pm
This query does what you requested with the provided dll and data.
;WITH cte(col) as (
SELECT col from #table1
UNION ALL
SELECT col from #table1
)
SELECT * ,
ROW_NUMBER() over (order by (select 0))
FROM CTE
masoudk1990 (9/13/2013)
And please help me with a general query that solve all same problems, not only a query that solve this particular problem, because my real table are more complex.
I have no idea what your other problems may be so it is highly unlikely that this one query will solve all your "same problems".
But if you post your other problems, I, and many, many others, would be happy to give it a go!
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
September 13, 2013 at 11:56 pm
Thank you very much for reply.
Im sorry I just didnt want to waste your precious time to solve my exact problem, but It seems I have to provide more informations.
I change my initializations to this:
CREATE TABLE #table1(col nvarchar(1));
CREATE TABLE #table2(col nvarchar(1));
INSERT INTO #table1 SELECT 'A' UNION ALL SELECT 'B' UNION ALL SELECT 'C';
INSERT INTO #table2 SELECT 'X' UNION ALL SELECT 'Y' UNION ALL SELECT 'Z';
Here is my sql statement:
WITH
cte1(col,Row_Num) as (
SELECT col, Row_Num= ROW_NUMBER() OVER (ORDER BY(SELECT 0)) FROM #table1
UNION ALL
SELECT col, Row_Num= ROW_NUMBER() OVER (ORDER BY(SELECT 0)) FROM #table1
),
cte2(col,Row_Num) as (
SELECT col, Row_Num= ROW_NUMBER() OVER (ORDER BY(SELECT 0)) FROM #table2
UNION ALL
SELECT col, Row_Num= ROW_NUMBER() OVER (ORDER BY(SELECT 0)) FROM #table2
)
SELECT cte1.col, cte2.col
FROM cte1
INNER JOIN cte2 ON cte1.ROW_NUM = cte2.ROW_NUM
In other case I want this output with above initializations:
col1,col2
---------
a,x
b,y
c,z
a,x
b,y
c,z
Sorry again because my first question was not provided enough information and wasted your time.
___________________________________
Computer Enterprise Masoud Keshavarz
I don't care about hell.
If I go there I've played enough Diablo to know how to fight my way out.
September 14, 2013 at 3:14 am
CREATE TABLE #table1(col nvarchar(1));
CREATE TABLE #table2(col nvarchar(1));
INSERT INTO #table1 SELECT 'A' UNION ALL SELECT 'B' UNION ALL SELECT 'C';
INSERT INTO #table2 SELECT 'X' UNION ALL SELECT 'Y' UNION ALL SELECT 'Z';
WITH
cte1(col,Row_Num) as (
SELECT col, Row_Num= ROW_NUMBER() OVER (ORDER BY(SELECT 0)) FROM #table1
),
cte2(col,Row_Num) as (
SELECT col, Row_Num= ROW_NUMBER() OVER (ORDER BY(SELECT 0)) FROM #table2
),
cte3 (col1, col2) AS (
SELECT cte1.col, cte2.col
FROM cte1
INNER JOIN cte2 ON cte1.Row_Num = cte2.Row_Num
)
SELECT col1, col2
FROM cte3
UNION ALL
SELECT col1, col2
FROM cte3
go
DROP TABLE #table1, #table2
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
September 14, 2013 at 7:02 am
Thank you everyone for giving me clue.
I solved this question with this statement:
WITH
cte1(col) as (
SELECT col FROM #table1
UNION ALL
SELECT col FROM #table1
),
cteA(col,Row_Num) as(
Select col, ROW_NUMBER() OVER (ORDER BY (SELECT 0))
FROM cte1 --here I used above cte :-)
),
cte2(col) as (
SELECT col FROM #table2
UNION ALL
SELECT col FROM #table2
),
cteB(col,Row_Num) as(
Select col, ROW_NUMBER() OVER (ORDER BY (SELECT 0))
FROM cte2 --here I used above cte :-)
)
SELECT cteA.col, cteB.col
FROM cteA
INNER JOIN cteB ON cteA.ROW_NUM = cteB.ROW_NUM
___________________________________
Computer Enterprise Masoud Keshavarz
I don't care about hell.
If I go there I've played enough Diablo to know how to fight my way out.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply