CODEWITHSUNDEEP
sqlsql-server
Eugene
Eugene

Reputation: 287

How to merge many(and different) cells from one row to many rows with fewer cells?

I'm getting lost at how accomplish such transformation, from(1 row with couples of the same type columns repeated 3 times):

NormalCol1 | NormalCol2 | col1_0 | col2_0 | col1_1 | col2_2 | col1_2 | col2_2
"AD"       | 2018-03-02 | "error"| "mess" | "warn" | "mess" | "info" | "mess"

to(3 rows with those 6 columns put together in 2):

NormalCol1 | NormalCol2 | col1   | col2 
"AD"       | 2018-03-02 | "error"| "mess"
"AD"       | 2018-03-02 | "warn" | "mess"
"AD"       | 2018-03-02 | "info" | "mess"

Have you ever stumbled upon such transformation? If yes, how did you accomplish the transformation?

Ps: the columns are fixed.

Upvotes: 0

Views: 64

Answers (2)

maulik kansara
maulik kansara

Reputation: 1107

You should go with UnPivot operation. Please take a note that here Where clause added in the end of query is most important. Without it, we haven't told SQL Server how the second set of unpivoted data matches the first. Without the WHERE , it will do a cross join, resulting in repetitive data for col1 for each unique data for col2. 

  declare @tbl as table
(
NormalCol1 varchar(50),
NormalCol2 varchar(50),
col1_0 varchar(50),
col2_0 varchar(50),
col1_1 varchar(50),
col2_1 varchar(50),
col1_2 varchar(50),
col2_2 varchar(50)
)

insert into @tbl
select 'AD','2018-03-02','error','mess','warn','mess','info','mess'



select NormalCol1,NormalCol2,col1,col2 from @tbl
unpivot
(
col1 for details in (col1_0,col1_1,col1_2)
)unp
unpivot
(
col2 for data in (col2_0,col2_1,col2_2)
)unp
WHERE RIGHT(details,1) = RIGHT(data,1)

Upvotes: 1

Squirrel
Squirrel

Reputation: 24783

use a UNION ALL query or CROSS APPLY (with union all also) or TABLE VALUE CONSTRUCTOR

SELECT NormalCol1, NormalCol2, col1 = col1_0, col2 = col2_0 FROM YourTable
UNION ALL
SELECT NormalCol1, NormalCol2, col1 = col1_1, col2 = col2_1 FROM YourTable
UNION ALL
SELECT NormalCol1, NormalCol2, col1 = col1_2, col2 = col2_2 FROM YourTable


SELECT NormalCol1, NormalCol2, col1, col2
FROM   YourTable
       CROSS APPLY
       (
           SELECT col1 = col1_0, col2 = col2_0
           UNION ALL
           SELECT col1 = col1_1, col2 = col2_1
           UNION ALL
           SELECT col1 = col1_2, col2 = col2_2
       ) a

SELECT NormalCol1, NormalCol2, col1, col2
FROM   YourTable
       CROSS APPLY
       (
           VALUES (col1_0, col2_0), 
                  (col1_1, col2_1),
                  (col1_2, col2_2)
       ) c (col1, col2)

Upvotes: 3

Related Questions