CODEWITHSUNDEEP
sqlsql-server
Arpit Tripathi
Arpit Tripathi

Reputation: 55

How to merge column this specific way in SQL

My original table is this; TableName= NewRetail

CustomerID  1    2   3      4    5     6      7.....30
    1       30  31   Null  Null Null   Null   Null
    2       24  78    35   72   Null   Null   Null

I want to store this table in 'Retail'

CustomerId Itemset 1 30 1 31 2 24 2 78 2 35 2 72

There are no duplicates in any row in Original(Source) Table.

Thanks. I tried using Loops but couldn't make it work. Been stuck at it since three days.

Upvotes: 2

Views: 66

Answers (3)

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

It is a simple UNPIVOT operation. NULLs automatically eliminated from result:

declare @t table(custid int, [1] int, [2] int, [3] int)

insert into @t values
(1, 10, 30, null),
(2, 30, 40, 50)


select custid, c from @t
unpivot(c for p in([1], [2], [3])) p

Fiddle http://sqlfiddle.com/#!6/9eecb/1733

Upvotes: 1

Pரதீப்
Pரதீப்

Reputation: 93694

You can use table valued constructor with Cross apply to unpivot the data

SELECT CustomerID,
       Itemset
FROM   Yourtable
       CROSS apply (VALUES ([1]),([2]),([3]),([4]),([5]),([6]),([7]),...) cs (Itemset)
WHERE  Itemset IS NOT NULL

Dynamic Version

DECLARE @itemset VARCHAR(max)='',
        @sql     NVARCHAR(max);

WITH cte
     AS (SELECT TOP 30 Row_number()OVER(ORDER BY (SELECT NULL)) RN
         FROM   sys.columns)
SELECT @itemset += '(' + Quotename(RN) + '),'
FROM   cte

SET @itemset = LEFT(@itemset, Len(@itemset) - 1)
SET @sql = 'SELECT CustomerID,
           Itemset
    FROM   Yourtable
           CROSS apply (VALUES ' + @itemset
           + ') cs (Itemset)
    WHERE  Itemset IS NOT NULL '

EXEC Sp_executesql @sql

Upvotes: 2

Felix Pamittan
Felix Pamittan

Reputation: 31879

You can do this using UNION ALL:

SELECT *
FROM (
    SELECT CustomerId, [1] AS ItemSet FROM NewRetail UNION ALL
    SELECT CustomerId, [2] FROM NewRetail UNION ALL
    SELECT CustomerId, [3] FROM NewRetail UNION ALL
    SELECT CustomerId, [4] FROM NewRetail UNION ALL
    SELECT CustomerId, [5] FROM NewRetail UNION ALL
    SELECT CustomerId, [6] FROM NewRetail UNION ALL
    SELECT CustomerId, [7] FROM NewRetail UNION ALL
    ...
    SELECT CustomerId, [30] FROM NewRetail
)t
WHERE ItemSet IS NOT NULL
ORDER BY CustomerId

Using dynamic SQL:

;WITH Tally(N) AS(
    SELECT TOP 30 ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM sys.columns
)
SELECT @sql = @sql +  
    CASE 
        WHEN @sql = '' THEN 'SELECT CustomerId, [' + CONVERT(VARCHAR(2), N) + '] AS ItemSet FROM NewRetail' 
        ELSE ' UNION ALL' + CHAR(10) +' SELECT CustomerId, [' + CONVERT(VARCHAR(2), N) + '] FROM NewRetail'
    END
    FROM Tally

SELECT @sql = 
'SELECT *
FROM (
    ' + @sql + CHAR(10) +
')t
WHERE ItemSet IS NOT NULL
ORDER BY CustomerId'

PRINT @sql
EXEC (@sql)

Upvotes: 1

Related Questions