Pivot a Non-numeric data table in SQL Server

Question

I am fairly new to SQL Server. I am trying to pivot this table on Part_Number so that the row and columns are interchanged. Notice how the Part_Number is repeated and the IBA_NAME column has all the properties.

PART_NUMBER IBA_NAME    PART_REVISION   PART_ITERATION  PART_NAME   IBAVALUE
161-83516-0014-M00  SPEED_GRADE A   3   MEM 32MX16 DDR2-533 266MHz 1.8V FBGA-84 PROMOS  -
161-83516-0014-M00  JEDEC_TYPE  A   3   MEM 32MX16 DDR2-533 266MHz 1.8V FBGA-84 PROMOS  -
161-83516-0014-M00  NV_LEAD_TIME    A   3   MEM 32MX16 DDR2-533 266MHz 1.8V FBGA-84 PROMOS  -
161-83516-0014-M00  VOLUME  A   3   MEM 32MX16 DDR2-533 266MHz 1.8V FBGA-84 PROMOS  -
161-83516-0014-M00  POW A   3   MEM 32MX16 DDR2-533 266MHz 1.8V FBGA-84 PROMOS  -
161-83516-0014-M00  LIBRARY A   3   MEM 32MX16 DDR2-533 266MHz 1.8V FBGA-84 PROMOS  N/A
161-83516-0014-M00  Valid_To    A   3   MEM 32MX16 DDR2-533 266MHz 1.8V FBGA-84 PROMOS  1111-11-11 00:00:00
161-83516-0014-M00  NV_COSTS    A   3   MEM 32MX16 DDR2-533 266MHz 1.8V FBGA-84 PROMOS  -
161-83516-0014-M00  ZZ_Item_Type    A   3   MEM 32MX16 DDR2-533 266MHz 1.8V FBGA-84 PROMOS  Part: Mem (Concept)
161-83516-0014-M00  NV_PREFERENCE   A   3   MEM 32MX16 DDR2-533 266MHz 1.8V FBGA-84 PROMOS  0
161-83516-0014-M00  SPARE   A   3   MEM 32MX16 DDR2-533 266MHz 1.8V FBGA-84 PROMOS  ROHS, PROMOS
161-83516-0014-M00  HEIGHT  A   3   MEM 32MX16 DDR2-533 266MHz 1.8V FBGA-84 PROMOS  -
161-83516-0014-M00  NAME    A   3   MEM 32MX16 DDR2-533 266MHz 1.8V FBGA-84 PROMOS  -
161-83516-0014-M00  PACK_TYPE   A   3   MEM 32MX16 DDR2-533 266MHz 1.8V FBGA-84 PROMOS  -
161-83516-0014-M00  QUALIFIER   A   3   MEM 32MX16 DDR2-533 266MHz 1.8V FBGA-84 PROMOS  -
161-83516-0014-M00  PTF_NAME    A   3   MEM 32MX16 DDR2-533 266MHz 1.8V FBGA-84 PROMOS  -
161-83516-0014-M00  Parts   A   3   MEM 32MX16 DDR2-533 266MHz 1.8V FBGA-84 PROMOS  Part: Mem (Concept)
161-83516-0014-M00  SPEED_GRADE A   4   MEM 32MX16 DDR2-533 266MHz 1.8V FBGA-84 PROMOS  -
161-83516-0014-M00  JEDEC_TYPE  A   4   MEM 32MX16 DDR2-533 266MHz 1.8V FBGA-84 PROMOS  -
161-83516-0014-M00  NV_LEAD_TIME    A   4   MEM 32MX16 DDR2-533 266MHz 1.8V FBGA-84 PROMOS  -
161-83516-0014-M00  VOLUME  A   4   MEM 32MX16 DDR2-533 266MHz 1.8V FBGA-84 PROMOS  -
161-83516-0014-M00  POW A   4   MEM 32MX16 DDR2-533 266MHz 1.8V FBGA-84 PROMOS  -
161-83516-0014-M00  LIBRARY A   4   MEM 32MX16 DDR2-533 266MHz 1.8V FBGA-84 PROMOS  N/A
161-83516-0014-M00  Valid_To    A   4   MEM 32MX16 DDR2-533 266MHz 1.8V FBGA-84 PROMOS  1111-11-11 00:00:00
161-83516-0014-M00  NV_COSTS    A   4   MEM 32MX16 DDR2-533 266MHz 1.8V FBGA-84 PROMOS  -
161-83516-0014-M00  ZZ_Item_Type    A   4   MEM 32MX16 DDR2-533 266MHz 1.8V FBGA-84 PROMOS  Part: Mem (Concept)
161-83516-0014-M00  NV_PREFERENCE   A   4   MEM 32MX16 DDR2-533 266MHz 1.8V FBGA-84 PROMOS  0
161-83516-0014-M00  SPARE   A   4   MEM 32MX16 DDR2-533 266MHz 1.8V FBGA-84 PROMOS  ROHS, PROMOS
161-83516-0014-M00  HEIGHT  A   4   MEM 32MX16 DDR2-533 266MHz 1.8V FBGA-84 PROMOS  -
161-83516-0014-M00  NAME    A   4   MEM 32MX16 DDR2-533 266MHz 1.8V FBGA-84 PROMOS  -
161-83516-0014-M00  PACK_TYPE   A   4   MEM 32MX16 DDR2-533 266MHz 1.8V FBGA-84 PROMOS  -
161-83516-0014-M00  QUALIFIER   A   4   MEM 32MX16 DDR2-533 266MHz 1.8V FBGA-84 PROMOS  -
161-83516-0014-M00  PTF_NAME    A   4   MEM 32MX16 DDR2-533 266MHz 1.8V FBGA-84 PROMOS  -
161-83516-0014-M00  Parts   A   4   MEM 32MX16 DDR2-533 266MHz 1.8V FBGA-84 PROMOS  Part: Mem (Concept)
161-83516-0014-M00  SPEED_GRADE A   5   MEM 32MX16 DDR2-533 266MHz 1.8V FBGA-84 PROMOS  -

I want the data to look like this - coloumnized based on IBA_NAME :

PART_NUMBER NAME    PACK_TYPE   QUALIFIER   PTF_NAME    SPEED_GRADE JEDEC_TYPE  NV_LEAD_TIME    VOLUME  POW LIBRARY

So far I tried writing the following code, but to no avail :

SELECT TOP (1000) 
    [PART_NUMBER],
    [PART_NAME], [PART_REVISION], [PART_ITERATION]
FROM 
    [PDPDATAMART].[dbo].[BI_PDP_partclass_d] 
PIVOT
    (MIN([VALUE])   
          FOR [PART_NUMBER] IN ([PART_NAME]) 
    ) AS p

Can anyone outline, what I am doing wrong and how I can fix the problem. Help would be appreciated:)

What is your expected output? please post it as a text table — Gurwinder Singh, Jan 26 '17 at 19:18

score 0 · Answer 1 · answered Jan 26 '17 at 20:36

This is what you want , just remove the pivoted columns that you dont need as I just did a quick and dirty and included all values in IBA_NAME :

SAMPLE DATA:

IF OBJECT_ID('tempdb..#temp') IS NOT NULL
    DROP TABLE #temp;

CREATE TABLE #temp(PART_NUMBER    VARCHAR(50)
              , IBA_NAME       VARCHAR(50)
              , PART_REVISION  VARCHAR(50)
              , PART_ITERATION VARCHAR(50)
              , PART_NAME      VARCHAR(50)
              , IBAVALUE       VARCHAR(50));

INSERT INTO #temp
VALUES
('161-83516-0014-M00','SPEED_GRADE','A',3,'MEM 32MX16 DDR2-533 266MHz 1.8V FBGA-84 PROMOS','-'),
('161-83516-0014-M00','JEDEC_TYPE','A',3,'MEM 32MX16 DDR2-533 266MHz 1.8V FBGA-84 PROMOS','-'),
('161-83516-0014-M00','NV_LEAD_TIME','A',3,'MEM 32MX16 DDR2-533 266MHz 1.8V FBGA-84 PROMOS','-'),
('161-83516-0014-M00','VOLUME','A',3,'MEM 32MX16 DDR2-533 266MHz 1.8V FBGA-84 PROMOS','-'),
('161-83516-0014-M00','POW','A',3,'MEM 32MX16 DDR2-533 266MHz 1.8V FBGA-84 PROMOS','-'),
('161-83516-0014-M00','LIBRARY','A',3,'MEM 32MX16 DDR2-533 266MHz 1.8V FBGA-84 PROMOS','N/A'),
('161-83516-0014-M00','Valid_To','A',3,'MEM 32MX16 DDR2-533 266MHz 1.8V FBGA-84 PROMOS','1111-11-11 00:00:00'),
('161-83516-0014-M00','NV_COSTS','A',3,'MEM 32MX16 DDR2-533 266MHz 1.8V FBGA-84 PROMOS','-'),
('161-83516-0014-M00','ZZ_Item_Type','A',3,'MEM 32MX16 DDR2-533 266MHz 1.8V FBGA-84 PROMOS','Part: Mem (Concept)'),
('161-83516-0014-M00','NV_PREFERENCE','A',3,'MEM 32MX16 DDR2-533 266MHz 1.8V FBGA-84 PROMOS','0'),
('161-83516-0014-M00','SPARE','A',3,'MEM 32MX16 DDR2-533 266MHz 1.8V FBGA-84 PROMOS','ROHS, PROMOS'),
('161-83516-0014-M00','HEIGHT','A',3,'MEM 32MX16 DDR2-533 266MHz 1.8V FBGA-84 PROMOS','-'),
('161-83516-0014-M00','NAME','A',3,'MEM 32MX16 DDR2-533 266MHz 1.8V FBGA-84 PROMOS','-'),
('161-83516-0014-M00','PACK_TYPE','A',3,'MEM 32MX16 DDR2-533 266MHz 1.8V FBGA-84 PROMOS','-'),
('161-83516-0014-M00','QUALIFIER','A',3,'MEM 32MX16 DDR2-533 266MHz 1.8V FBGA-84 PROMOS','-'),
('161-83516-0014-M00','PTF_NAME','A',3,'MEM 32MX16 DDR2-533 266MHz 1.8V FBGA-84 PROMOS','-'),
('161-83516-0014-M00','Parts','A',3,'MEM 32MX16 DDR2-533 266MHz 1.8V FBGA-84 PROMOS','Part: Mem (Concept)'),
('161-83516-0014-M00','SPEED_GRADE','A',4,'MEM 32MX16 DDR2-533 266MHz 1.8V FBGA-84 PROMOS','-'),
('161-83516-0014-M00','JEDEC_TYPE','A',4,'MEM 32MX16 DDR2-533 266MHz 1.8V FBGA-84 PROMOS','-'),
('161-83516-0014-M00','NV_LEAD_TIME','A',4,'MEM 32MX16 DDR2-533 266MHz 1.8V FBGA-84 PROMOS','-'),
('161-83516-0014-M00','VOLUME','A',4,'MEM 32MX16 DDR2-533 266MHz 1.8V FBGA-84 PROMOS','-'),
('161-83516-0014-M00','POW','A',4,'MEM 32MX16 DDR2-533 266MHz 1.8V FBGA-84 PROMOS','-'),
('161-83516-0014-M00','LIBRARY','A',4,'MEM 32MX16 DDR2-533 266MHz 1.8V FBGA-84 PROMOS','N/A'),
('161-83516-0014-M00','Valid_To','A',4,'MEM 32MX16 DDR2-533 266MHz 1.8V FBGA-84 PROMOS','1111-11-11 00:00:00'),
('161-83516-0014-M00','NV_COSTS','A',4,'MEM 32MX16 DDR2-533 266MHz 1.8V FBGA-84 PROMOS','-'),
('161-83516-0014-M00','ZZ_Item_Type','A',4,'MEM 32MX16 DDR2-533 266MHz 1.8V FBGA-84 PROMOS','Part: Mem (Concept)'),
('161-83516-0014-M00','NV_PREFERENCE','A',4,'MEM 32MX16 DDR2-533 266MHz 1.8V FBGA-84 PROMOS','0'),
('161-83516-0014-M00','SPARE','A',4,'MEM 32MX16 DDR2-533 266MHz 1.8V FBGA-84 PROMOS','ROHS, PROMOS'),
('161-83516-0014-M00','HEIGHT','A',4,'MEM 32MX16 DDR2-533 266MHz 1.8V FBGA-84 PROMOS','-'),
('161-83516-0014-M00','NAME','A',4,'MEM 32MX16 DDR2-533 266MHz 1.8V FBGA-84 PROMOS','-'),
('161-83516-0014-M00','PACK_TYPE','A',4,'MEM 32MX16 DDR2-533 266MHz 1.8V FBGA-84 PROMOS','-'),
('161-83516-0014-M00','QUALIFIER','A',4,'MEM 32MX16 DDR2-533 266MHz 1.8V FBGA-84 PROMOS','-'),
('161-83516-0014-M00','PTF_NAME','A',4,'MEM 32MX16 DDR2-533 266MHz 1.8V FBGA-84 PROMOS','-'),
('161-83516-0014-M00','Parts','A',4,'MEM 32MX16 DDR2-533 266MHz 1.8V FBGA-84 PROMOS','Part: Mem (Concept)'),
('161-83516-0014-M00','SPEED_GRADE','A',5,'MEM 32MX16 DDR2-533 266MHz 1.8V FBGA-84 PROMOS','-')

QUERY:

SELECT [PART_NAME]
    , [PART_REVISION]
    , [PART_ITERATION]
    , [SPEED_GRADE]
    , [JEDEC_TYPE]
    , [NV_LEAD_TIME]
    , [VOLUME]
    , [POW]
    , [LIBRARY]
    , [Valid_To]
    , [NV_COSTS]
    , [ZZ_Item_Type]
    , [NV_PREFERENCE]
    , [SPARE]
    , [HEIGHT]
    , [NAME]
    , [PACK_TYPE]
    , [QUALIFIER]
    , [PTF_NAME]
    , [Parts]
FROM
      (SELECT *
       FROM   #temp) AS SourceTable PIVOT(MIN(IBAVALUE) FOR IBA_NAME IN([SPEED_GRADE]
                                                         , [JEDEC_TYPE]
                                                         , [NV_LEAD_TIME]
                                                         , [VOLUME]
                                                         , [POW]
                                                         , [LIBRARY]
                                                         , [Valid_To]
                                                         , [NV_COSTS]
                                                         , [ZZ_Item_Type]
                                                         , [NV_PREFERENCE]
                                                         , [SPARE]
                                                         , [HEIGHT]
                                                         , [NAME]
                                                         , [PACK_TYPE]
                                                         , [QUALIFIER]
                                                         , [PTF_NAME]
                                                         , [Parts])) AS PivotTable;

RESULT:

Pivot a Non-numeric data table in SQL Server

1 Answers1