CODEWITHSUNDEEP
sqlsql-servert-sqlpivot
Roger123
Roger123

Reputation: 1

SQL Pivot returning the same value

I am trying to run a pivot on a table which looks like the below.

Item    Barcode Primary
Test1   111111  Y
Test1   222222  N
Test1   333333  N
Test1   444444  N
Test2   999999  Y
Test2   888888  N
Test2   777777  N
Test2   666666  N

I am trying to create a table which looks like this. (one barcode per column) 

Item    Primary Sec1    Sec2    Sec3  
Test1   111111  222222  333333  444444
Test2   999999  888888  777777  666666

Using the below query i can populate the primary column and the first secondary column, but I cant find a way to bring the other secondary codes up to their own column. Instead, it simply repeats the first secondary barcode like the below.

Item    Primary Barcode Secondary 1 Secondary3
Test1   111111  222222  222222
Test2   999999  888888  888888

select item, [Y] as 'Primary', [N] as 'Sec1', [N] as 'Sec2'
from
(
    select ics.itemcoloursize_id as item,  sc.sellcode_code as code, sc.primary_ind as ind
    from sellcode sc
    left join itemcoloursize ics on ics.itemcoloursize_id = sc.itemcoloursize_id
    where ics.itemcoloursize_id in 
    (
    's+0015p00fhb'
    )  
)   as Barcodes
PIVOT
(
    max(code)
    for ind in ([Y],[N])
    )
    as PVT

Any help would be appreciated.

Upvotes: 0

Views: 221

Answers (3)

KumarHarsh
KumarHarsh

Reputation: 5094

Try this script and lemme know if any error or output is incorrect with other sample data,

Also it is advisable to use sp_executesql

CREATE TABLE #t (Item VARCHAR(50)
,Barcode VARCHAR(50),Primarys CHAR(1))

INSERT INTO #t VALUES   
 ('Test1','111111','Y')
,('Test1','222222','N')
,('Test1','333333','N')
,('Test1','444444','N')
,('Test2','999999','Y')
,('Test2','888888','N')
,('Test2','777777','N')
--,('Test2','666666','N')

--SELECT * FROM #t

DECLARE @ColValue VARCHAR(500)
DECLARE @ColName VARCHAR(500)

;WITH CTE
AS (
    SELECT Item
        ,Barcode
        ,Primarys
        ,ROW_NUMBER() OVER (
            PARTITION BY item ORDER BY item
            ) rn
    FROM #t
    )
    ,CTE1
AS (
    SELECT Item
        ,Barcode
        ,Primarys
        ,rn
        ,CASE 
            WHEN Primarys = 'Y'
                THEN 'Primary'
            ELSE 'Sec' + cast(rn - 1 AS VARCHAR)
            END Headers
    FROM cte
    )
SELECT *
INTO #tt
FROM cte1


SELECT @ColName = stuff((
            SELECT ',[' + Headers + ']'
            FROM #tt t1
            WHERE t.Item = t1.Item
            FOR XML PATH('')
            ), 1, 1, '')
FROM #tt t
ORDER BY rn

--SELECT @ColName
DECLARE @Sql VARCHAR(500) = ''

SET @Sql = '
select item,' + @ColName + ' from 
(
select item,Barcode,Headers from #tt
)base
pivot(max(Barcode) for Headers in(' + @ColName + ')) as pvt
'

PRINT @sql

EXEC (@Sql)

DROP TABLE #tt

DROP TABLE #t

Here #t is your table.leave #tt as it is.It can be further optimize depending on other thing.

Upvotes: 0

Deepan Chakravarthy
Deepan Chakravarthy

Reputation: 1

Try this and may be helpful to you.

Solution:
combine barcode values by dot separator group by item
then separate the barcodes as an individual column by using parsename function

    select  item,parsename(commasep,4)Primry,parsename(commasep,3)sec1, 
    parsename(commasep,2)sec2,parsename(commasep,1)sec3
    from
     (select item,stuff((select '.' + u.barcode from pivottest u
     where u.item = p.item order by u.isprimary desc
     for xml path('')),1,1,'') as commasep
     from pivottest p group by item) as parsecommasep

RESULT:

item Primry sec1 sec2 sec3 Test 1 111111 222222 333333 444444 Test 2 999999 888888 777777 666666

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

You could try conditional aggregation with the help of case expression if you don't want to go with pivot or some dynamic approach 

select Item,
      max(case when [Primary] = 'Y' then Barcode else null end) [Primary],
      max(case when ([Primary] = 'N' AND [RN] = 1) then Barcode else null end) [Sec1],
      max(case when ([Primary] = 'N' AND [RN] = 2) then Barcode else null end) [Sec2],
      max(case when ([Primary] = 'N' AND [RN] = 3) then Barcode else null end) [Sec3]  
      from 
(
    select *, row_number() over (partition by Item, [Primary] order by Item) [rn] from table
) t
group by Item

Result :

Item    Primary Sec1    Sec2    Sec3
Test1   111111  222222  333333  444444
Test2   999999  888888  777777  666666

Upvotes: 1

Related Questions