combine 2 columns... - 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)

combine 2 columns...

Author

Topic

dbonneau
Yak Posting Veteran

50 Posts

Posted - 2008-01-28 : 15:01:40

Hi,
I have 2 columns in a table that needs to be combined as follow.
Could anyone help me on this?

Thanks!

col1
----
1
2
3
4
5
.
.
X

col2
----
a
b
c
d
e
.
.
.
Y

combine
------
a
1
b
2
c
3
d
4
e
5
.
.
Y
X

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-01-28 : 15:11:51

Here is one way to do it:

DECLARE @a TABLE ( col1 INT )
DECLARE @b TABLE ( col2 CHAR(1) )

INSERT @a ( col1 )
SELECT 1 UNION
SELECT 2 UNION
SELECT 3 UNION
SELECT 4 UNION
SELECT 5 

INSERT @b ( col2 )
SELECT 'a' UNION
SELECT 'b' UNION
SELECT 'c' UNION
SELECT 'd' UNION
SELECT 'e'


SELECT  col
FROM    ( SELECT    row_number() OVER ( ORDER BY col1 ) AS rownbr,
                    CAST(col1 AS VARCHAR) AS col,
                    0 AS listid
          FROM      @a
          UNION
          SELECT    row_number() OVER ( ORDER BY col2 ) AS rownbr,
                    CAST(col2 AS VARCHAR),
                    1
          FROM      @b
        ) a
ORDER BY rownbr,
        listid DESC

Edit: Add a column to order by ( listid ).

dbonneau
Yak Posting Veteran

50 Posts

Posted - 2008-01-28 : 17:23:56

Thank you for the code jdaman!

Is there any other ways to combine them together if I had more than 100,000 records in each column? I think the logic you gave me works good for few rows.

Thanks again!

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-01-28 : 17:31:09

quote:
Originally posted by dbonneau

Thank you for the code jdaman!

Is there any other ways to combine them together if I had more than 100,000 records in each column? I think the logic you gave me works good for few rows.

Thanks again!

The logic should work regardless of the number of rows. Have you tried it on a large dataset and not gotten your expected result?

For instance the above code will work just fine on the following large dataset:

DECLARE @a TABLE ( col1 FLOAT )
DECLARE @b TABLE ( col2 CHAR(4) )

INSERT @a ( col1 )
SELECT n0 + n1 + n2 + n3 + n4 + n5 + n6 + n7 + n8
   FROM (SELECT 0 AS n0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3) AS z0
   CROSS JOIN (SELECT 0 AS n1 UNION SELECT 4 UNION SELECT 8 UNION SELECT 12) AS z1
   CROSS JOIN (SELECT 0 AS n2 UNION SELECT 16 UNION SELECT 32 UNION SELECT 48) AS z2
   CROSS JOIN (SELECT 0 AS n3 UNION SELECT 64 UNION SELECT 128 UNION SELECT 192) AS z3
   CROSS JOIN (SELECT 0 AS n4 UNION SELECT 256 UNION SELECT 512 UNION SELECT 768) AS z4
   CROSS JOIN (SELECT 0 AS n5 UNION SELECT 1024 UNION SELECT 2048 UNION SELECT 3072) AS z5
   CROSS JOIN (SELECT 0 AS n6 UNION SELECT 4096 UNION SELECT 8192 UNION SELECT 12288) AS z6
   CROSS JOIN (SELECT 0 AS n7 UNION SELECT 16384 UNION SELECT 32768 UNION SELECT 49152) AS z7
   CROSS JOIN (SELECT 0 AS n8 UNION SELECT 65536 UNION SELECT 131072 UNION SELECT 196608) AS z8

INSERT @b ( col2 )
SELECT c0 + c1 + c2 + c3
   FROM (SELECT 'a' AS c0 UNION SELECT 'b' UNION SELECT 'c' UNION SELECT 'd' UNION SELECT 'e' UNION SELECT 'f' UNION SELECT 'g' UNION SELECT 'h' UNION SELECT 'i' UNION SELECT 'j' UNION SELECT 'k' UNION SELECT 'l' UNION SELECT 'm' UNION SELECT 'n' UNION SELECT 'o' UNION SELECT 'p' UNION SELECT 'q' UNION SELECT 'r' UNION SELECT 's' UNION SELECT 't' UNION SELECT 'u' UNION SELECT 'v' UNION SELECT 'w' UNION SELECT 'x' UNION SELECT 'y' UNION SELECT 'z') AS z0
   CROSS JOIN (SELECT 'a' AS c1 UNION SELECT 'b' UNION SELECT 'c' UNION SELECT 'd' UNION SELECT 'e' UNION SELECT 'f' UNION SELECT 'g' UNION SELECT 'h' UNION SELECT 'i' UNION SELECT 'j' UNION SELECT 'k' UNION SELECT 'l' UNION SELECT 'm' UNION SELECT 'n' UNION SELECT 'o' UNION SELECT 'p' UNION SELECT 'q' UNION SELECT 'r' UNION SELECT 's' UNION SELECT 't' UNION SELECT 'u' UNION SELECT 'v' UNION SELECT 'w' UNION SELECT 'x' UNION SELECT 'y' UNION SELECT 'z') AS z1
   CROSS JOIN (SELECT 'a' AS c2 UNION SELECT 'b' UNION SELECT 'c' UNION SELECT 'd' UNION SELECT 'e' UNION SELECT 'f' UNION SELECT 'g' UNION SELECT 'h' UNION SELECT 'i' UNION SELECT 'j' UNION SELECT 'k' UNION SELECT 'l' UNION SELECT 'm' UNION SELECT 'n' UNION SELECT 'o' UNION SELECT 'p' UNION SELECT 'q' UNION SELECT 'r' UNION SELECT 's' UNION SELECT 't' UNION SELECT 'u' UNION SELECT 'v' UNION SELECT 'w' UNION SELECT 'x' UNION SELECT 'y' UNION SELECT 'z') AS z2
   CROSS JOIN (SELECT 'a' AS c3 UNION SELECT 'b' UNION SELECT 'c' UNION SELECT 'd' UNION SELECT 'e' UNION SELECT 'f' UNION SELECT 'g' UNION SELECT 'h' UNION SELECT 'i' UNION SELECT 'j' UNION SELECT 'k' UNION SELECT 'l' UNION SELECT 'm' UNION SELECT 'n' UNION SELECT 'o' UNION SELECT 'p' UNION SELECT 'q' UNION SELECT 'r' UNION SELECT 's' UNION SELECT 't' UNION SELECT 'u' UNION SELECT 'v' UNION SELECT 'w' UNION SELECT 'x' UNION SELECT 'y' UNION SELECT 'z') AS z3

dbonneau
Yak Posting Veteran

50 Posts

Posted - 2008-01-28 : 18:03:50

quote:
Originally posted by jdaman

quote:
Originally posted by dbonneau

Thank you for the code jdaman!

Is there any other ways to combine them together if I had more than 100,000 records in each column? I think the logic you gave me works good for few rows.

Thanks again!

The logic should work regardless of the number of rows. Have you tried it on a large dataset and not gotten your expected result?

Actually, My dataset look like this. Neither they are in order nor in sequence.
Col1 Col2
----- ---------
S1756 61745ML43
S1767 36228CTH6
S1769 361849F56
S1770 61745MQ89
S1890 59022HEX6
S2117 07383FYH5
S2113 61745MT45
S2116 929766C43
S2120 929766W58

. .
. .
SXXXX YYYYYYYYY

So I don’t know how I should put all of them in your select logic unless I write all of them one by one as below.

INSERT @a ( col1 )
SELECT S1756 UNION
SELECT S1767 UNION
SELECT S1769 UNION
SELECT S1770 UNION
SELECT S1890
.
.

INSERT @b ( col2 )
SELECT '61745ML43' UNION
SELECT '36228CTH6' UNION
SELECT '361849F56' UNION
SELECT '61745MQ89' UNION
SELECT '59022HEX6'

Thanks !

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-01-28 : 18:30:19

Im assuming Col1 needs to correspond with Col2 from the same row.
Since we need to tie the columns together as a pair its written a bit
differently but using the same concept.

/*
Representation of your existing table:
*/
DECLARE @YourTable TABLE ( Col1 VARCHAR(5), Col2 VARCHAR(9) )
INSERT @YourTable ( Col1, Col2 )
SELECT 'S1756', '61745ML43' UNION
SELECT 'S1767', '36228CTH6' UNION
SELECT 'S1769', '361849F56' UNION
SELECT 'S1770', '61745MQ89' UNION
SELECT 'S1890', '59022HEX6' UNION
SELECT 'S2117', '07383FYH5' UNION
SELECT 'S2113', '61745MT45' UNION
SELECT 'S2116', '929766C43' UNION
SELECT 'S2120', '929766W58'

SELECT  col
FROM    ( SELECT    CAST(Col1 AS VARCHAR) AS col,
                    rownbr,
                    0 AS listid
          FROM      ( SELECT    row_number() OVER ( ORDER BY Col1, Col2 ) AS rownbr,
                                Col1
                      FROM      @YourTable
                    ) a
          UNION
          SELECT    CAST(Col2 AS VARCHAR),
                    rownbr,
                    1
          FROM      ( SELECT    row_number() OVER ( ORDER BY Col1, Col2 ) AS rownbr,
                                Col2
                      FROM      @YourTable
                    ) a
        ) a
ORDER BY rownbr,
        listid DESC

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources