0

I have a table like this:

enter image description here

I need to get output like this:

enter image description here

I tried to use transposing, but I am not getting someone please give idea to do this.

Creation script for table:

CREATE TABLE #T1 (
    itemid int,
    pack int,
    UOM nvarchar(2)
)

INSERT INTO #T1 VALUES
(1,1,'EA'),
(1,10,'BX'),
(1,100,'CA'),
(2,1,'EA'),
(2,10,'RL')
1
  • i need to write a query, the output must be like second table Commented Aug 26, 2016 at 7:05

3 Answers 3

1

If you need simple, fixed column solution, you can use:

SELECT * INTO #temp FROM
(VALUES 
    (1, 1, 'EA'),
    (1, 10, 'BX'),
    (1, 100, 'CA'),
    (2, 1, 'EA'),
    (2, 10, 'RL')) T(ItemId, Pack, UOM)

SELECT ItemId, MAX([1]) Pack1, MAX([U1]) UOM1, MAX([2]) Pack1, MAX([U2]) UOM1, MAX([3]) Pack1, MAX([U3]) UOM1 FROM
(
    SELECT *,
        ROW_NUMBER() OVER (PARTITION BY ItemId ORDER BY Pack) PackNum,
        'U'+CONVERT(varchar(10), ROW_NUMBER() OVER (PARTITION BY ItemId ORDER BY Pack)) UOMNum
    FROM #temp
) T
PIVOT
(
    MAX(Pack) FOR PackNum IN ([1], [2], [3])
) PPack
PIVOT
(
    MAX(UOM) FOR UOMNum IN ([U1],[U2],[U3])
) PUOM
GROUP BY ItemId

If number of columns is not fixed, you must do it dynamically (see @gofr1) or:

DECLARE @count TABLE(N varchar(10))
    INSERT @count
    SELECT CONVERT(varchar(10), ROW_NUMBER() OVER (ORDER BY (SELECT 1)))
    FROM #temp WHERE ItemId =
    (
        SELECT TOP 1 ItemId
        FROM #temp
        GROUP BY ItemId
        ORDER BY COUNT(*) DESC
    )

DECLARE @select varchar(MAX) = STUFF((SELECT ', MAX(['+N+']) Pack'+N+', MAX([U'+N+']) UOM'+N FROM @count FOR XML PATH('')), 1, 2, '')
DECLARE @pivot1 varchar(MAX) = STUFF((SELECT ', ['+N+']' FROM @count FOR XML PATH('')), 1, 2, '')
DECLARE @pivot2 varchar(MAX) = STUFF((SELECT ', [U'+N+']' FROM @count FOR XML PATH('')), 1, 2, '')

DECLARE @sql varchar(MAX) = '
SELECT ItemId, '+@select+' FROM
(
    SELECT *,
        ROW_NUMBER() OVER (PARTITION BY ItemId ORDER BY Pack) PackNum,
        ''U''+CONVERT(varchar(10), ROW_NUMBER() OVER (PARTITION BY ItemId ORDER BY Pack)) UOMNum
    FROM #temp
) T
PIVOT
(
    MAX(Pack) FOR PackNum IN ('+@pivot1+')
) PPack
PIVOT
(
    MAX(UOM) FOR UOMNum IN ('+@pivot2+')
) PUOM
GROUP BY ItemId'

EXEC(@sql)
Sign up to request clarification or add additional context in comments.

1 Comment

Can anyone suggest me without using pivot, how shall i acieve it?
1

Dynamic SQL and some pivoting:

DECLARE @sql nvarchar(max),
        @columns nvarchar(max)

SELECT @columns = (
    SELECT DISTINCT  ','+QUOTENAME('pack' + cast(ROW_NUMBER() OVER (PARTITION BY itemid ORDER BY itemid) as nvarchar(max)))+','+
            QUOTENAME('UOM'+ cast(ROW_NUMBER() OVER (PARTITION BY itemid ORDER BY itemid) as nvarchar(max)))
    FROM #T1
    FOR XML PATH('')
)

SELECT @sql = N'
SELECT *
FROM (
    SELECT  itemid,
            Items+rn as Items,
            [Values]
    FROM (
        SELECT  itemid,
                CAST(UOM as nvarchar(max)) as UOM,
                CAST(pack as nvarchar(max)) as pack,
                CAST(ROW_NUMBER() OVER (PARTITION BY itemid ORDER BY (SELECT NULL)) as nvarchar(max)) as rn
        FROM #T1
        ) as t
    UNPIVOT (
        [Values] FOR Items in ([UOM],[pack])
    ) as up
    ) t1
PIVOT (
    MAX([Values]) FOR Items IN ('+STUFF(@columns,1,1,'')+')
) as pvt'

EXEC sp_executesql @sql

Output:

itemid  pack1   UOM1    pack2   UOM2    pack3   UOM3
1       1       EA      10      BX      100     CA
2       1       EA      10      RL      NULL    NULL

EDIT#1

There is one more way:

DECLARE @sql nvarchar(max),
        @columns nvarchar(max),
        @colsrn nvarchar(max)

;WITH cte AS (
SELECT DISTINCT cast(ROW_NUMBER() OVER (PARTITION BY itemid ORDER BY itemid) as nvarchar(max)) as rn
FROM #T1
)

SELECT @columns = (
    SELECT ',MAX('+QUOTENAME('pack' + rn) +') as '+ QUOTENAME('pack' + rn) +',MAX('+
            QUOTENAME('UOM'+ rn) +') as ' + QUOTENAME('UOM'+ rn)
    FROM cte
    FOR XML PATH('')
),
        @colsrn = (
    SELECT DISTINCT  ',CASE WHEN rn = ' + rn +' THEN '+QUOTENAME('pack')+' ELSE NULL END as [pack'+rn+'],' 
                    +'CASE WHEN rn = ' + rn +' THEN '+QUOTENAME('UOM')+' ELSE NULL END as [UOM'+rn+']'  
    FROM cte
    FOR XML PATH('')
)

SELECT @sql = N'
SELECT  itemid'+@columns+'
FROM (
    SELECT  itemid'+@colsrn+'
    FROM (
            SELECT  *,
                    ROW_NUMBER() OVER (PARTITION BY itemid ORDER BY (SELECT NULL)) as rn
            FROM #T1
            ) as t
    ) s
GROUP BY s.itemid'

EXEC sp_executesql @sql

Same output.

6 Comments

Are you serious? :) the simplest way is to put your table in Excel and pivot there. If the number of packs and UOMs is maximum 3 or 5 you can use query inside SELECT @sql = N' and change '+STUFF(@columns,1,1,'')+' to [pack1],[UOM1],...[packN],[UOMN] and here it is.
will you help me to do it by using rowid, select and few cases?
i am getting error like this "The type of column "pack" conflicts with the type of other columns specified in the UNPIVOT list."
What is the type of pack and UOM columns? When UNPIVOTing - there columns must be converted/casted in same datatype. I used int for pack and nvarchar(max) for UOM when created temp table and as you can see I am CAST(pack as nvarchar(max)) as pack, in the UNPIVOT part.
In the inner select list instead UOM use CAST(UOM as nvarchar(max)) UOM. That should solve conflict.
|
0
select itemid,
max(case when seq = 1 then pack end) pack1,  max(case when seq = 1 then uom end)uom1,max(case when seq = 2 then pack end) pack2, max(case when seq = 2 then uom end) uom2,
  max(case when seq = 3 then pack end) pack3, max(case when seq = 3 then uom end) uom3
from 
(
  select itemid, pack, uom, row_number() over(partition by itemid order by itemid) seq from T1
) d
group by itemid

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.