Transform a table within SQL Server

Question

I have a table test looks like

Month|CA     |CATTC | CA     |CATTC
------------------------------------
1    |100    |20    | 250    |120  
5    |100    |30    | 202    |140  
12   |130    |260   | 255    |130

My goal is to get a table test 2 like

Month|CA     |CATTC    
--------------------
1    |100    |20       
5    |100    |30        
12   |130    |260        
1    |250    |120   
5    |202    |140   
12   |255    |130

Is it possible within SQL Server?

You can't have the same column name twice. How are we supposed to chose one of the two CA's? — jarlh
– jarlh, Commented Jul 7, 2015 at 8:12

knkarthick24 · Accepted Answer · 2015-07-07 08:47:38Z

2

Try this,

CREATE TABLE #TEMP
(
    [Month] INT,
    CA INT,
    CASTTC INT,
    CA1 INT,
    CATTC1 INT
)


INSERT INTO #TEMP VALUES
(1    ,100    ,20    , 250    ,120),  
(5    ,100    ,30    , 202    ,140),  
(12   ,130    ,260   , 255    ,130) 

SELECT [Month],CrossApplied.CA,CrossApplied.CASTTC FROM #TEMP
CROSS APPLY (VALUES (CA,CASTTC),(CA1,CATTC1)) CrossApplied(CA,CASTTC)

(OR)

SELECT [Month],
       CrossApplied.CA,
       CrossApplied.CASTTC
FROM   #TEMP
       CROSS APPLY (SELECT CA,
                           CASTTC
                    UNION ALL
                    SELECT CA1,
                           CATTC1) CrossApplied(CA, CASTTC)

edited Jul 7, 2015 at 8:47

knkarthick24

3,24619 silver badges21 bronze badges

answered Jul 7, 2015 at 8:27

PP006

7097 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ughai Over a year ago

why use both CROSS APPLY and UNION ALL in the same query?

jarlh · Accepted Answer · 2015-07-07 08:13:15Z

2

Change columns names, then do a UNION ALL:

select Month, CA1 as CA, CATTC1  as CATTC from tablename
UNION ALL
select Month, CA2, CATTC2 from tablename

answered Jul 7, 2015 at 8:13

jarlh

44.9k8 gold badges52 silver badges68 bronze badges

Comments

user2221088 · Accepted Answer · 2015-07-07 09:25:32Z

0

CATTC column is dublicate, change to CATTC_ and use "union all"

CREATE TABLE #dt
(
    [Month] INT,
    CA INT,
    CATTC INT,
    CA1 INT,
    CATTC_ INT
)

INSERT INTO #dt VALUES
(1    ,100    ,20    , 250    ,120),  
(5    ,100    ,30    , 202    ,140),  
(12   ,130    ,260   , 255    ,130) 

select [Month], CA, CATTC  from #dt
union all 
select [Month], CA, CATTC_ from #dt

answered Jul 7, 2015 at 9:25

user2221088

Collectives™ on Stack Overflow

Transform a table within SQL Server

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related