Clever Geek Handbook

SQL - two columns into one separate custom column

If I have a table like this:

Col 1 | Col 2
-------------
   A  |  1
   A  |  2
   B  |  1
   C  |  1
   C  |  2
   C  |  3

How can I write a query to pull one column that looks like this:

Col 1 
------
  A
  1
  2
  B
  1
  C
  1
  2
  3
+3


source to share


3 answers 


SELECT col1
FROM Some_Table_You_Did_Not_Name

UNION ALL

SELECT col2
FROM Some_Table_You_Did_Not_Name

If order matters in your example, you want:



WITH data AS
(
   SELECT col1, col2, ROW_NUMBER() OVER (ORDER BY col1, col2) as RN
   FROM Some_Table_You_Did_Not_Name
)
SELECT col
FROM (
    SELECT DISTINCT col1 as col, RN, 1 as O
    FROM data

    UNION ALL

    SELECT DISTINCT col2 as col, RN, 2 as O
    FROM data
) JC_IS_THAT_GUY
ORDER BY RN ASC, O ASC, col ASC
+4


source


You can use the following query:

SELECT Col1
FROM (
   SELECT DISTINCT Col1, Col1 AS Col2, 0 AS grp
   FROM mytable

   UNION ALL

   SELECT Col2 AS Col1, Col1 AS Col2, 1 AS grp
   FROM mytable) AS t
ORDER BY Col2, grp, Col1


Demo here

+2


source


Absolutely no need to do UNION, UNION ALL, or reference the table multiple times to disable data ...

-- if Col2 is always a well ordered sequense like the test data...
SELECT 
    Col1 = x.Value
FROM 
    #TestData td
    CROSS APPLY ( VALUES (IIF(td.Col2 = 1, td.Col1, NULL)), (CAST(td.Col2 AS CHAR(1))) ) x (Value)
WHERE
    x.Value IS NOT NULL;

-- if it isn't...
WITH 
    cre_Add_RN AS (
        SELECT 
            td.Col1,
            td.Col2,
            RN = ROW_NUMBER() OVER (PARTITION BY td.Col1 ORDER BY td.Col2)
        FROM 
            #TestData td
        )
SELECT 
    x.Value
FROM 
    cre_Add_RN arn
    CROSS APPLY ( VALUES (IIF(arn.RN = 1, arn.Col1, NULL)), (CAST(arn.Col2 AS CHAR(1))) ) x (Value)
WHERE 
    x.Value IS NOT NULL;

NTN, Jason

0


source






More articles:


All Articles