locked
show row data from sql table as columns RRS feed

  • Question

  • User-2145645956 posted

    I have table data as following 

    EuipmentName MAKE N01 ModelNo ValidDate By
    VITAL SIGN SIMULATOR FLUKE PROSIM/26567 12/05/2016 HLL
    SPO2 ANALYZER FLUKE SPOT LIGHT/2955070 12/05/2016 HLL
    ELECTRICAL SAFETY ANALYZER FLUKE ESA612/2959012 12/05/2016 HLL
    THERMO HYGROMETER FANEM MODE3620/RAH85394 12/05/2016 FCRI PALAKKAD
    EQUIPMENTSAMPLE MAKE N01 MODEL NO1 12/10/2016 HLL

    and  I need the result as like below sample

    HDR Epq1 Epq2 Epq3 Epq4 Epq5
    EuipmentName VITAL SIGN SIMULATOR SPO2 ANALYZER ELECTRICAL SAFETY ANALYZER THERMO HYGROMETER EQUIPMENTSAMPLE
    MAKE N01 FLUKE FLUKE FLUKE FANEM MAKE N01
    ModelNo PROSIM/26567 SPOT LIGHT/2955070 ESA612/2959012 MODE3620/RAH85394 MODEL NO1
    ValidDate 12/05/2016 12/05/2016 12/05/2016 12/05/2016 12/05/2016
    By HLL HLL HLL FCRI PALAKKAD HLL

    columns excep HDR are dynamic based on the table data

    please help as early as possible

    Tuesday, January 3, 2017 9:37 AM

All replies

  • User-2145645956 posted

    I try with pivot table query but i didn't get result like i mentioned

    Tuesday, January 3, 2017 11:41 AM
  • User-2057865890 posted

    Hi Smiletech,

    declare @tb table
    (EuipmentName varchar(30),
    MAKEN01 varchar(30),
    ModelNo varchar(30),
    ValidDate varchar(30),	
    y varchar(30),
    name varchar(30)
    )
    
    insert into @tb
    values
    ('VITAL SIGN SIMULATOR','FLUKE','PROSIM/26567','12/05/2016','HLL','Epq1'),
    ('SPO2 ANALYZER','FLUKE','SPOT LIGHT/2955070','12/05/2016','HLL','Epq2'),
    ('ELECTRICAL SAFETY ANALYZER',	'FLUKE','ESA612/2959012','12/05/2016','HLL','Epq2'),
    ('THERMO HYGROMETER','FANEM','MODE3620/RAH85394','12/05/2016','FCRI PALAKKAD','Epq3'),
    ('EQUIPMENTSAMPLE','MAKE N01','MODEL NO1','12/10/2016','HLL','Epq4')
    
    --UNPIVOT
    --select  Address0,Address,name from 
    --(select EuipmentName,MAKEN01,ModelNo,ValidDate,y,name from @tb pvt) p
    --unpivot
    --(Address for Address0 in (EuipmentName,MAKEN01,ModelNo,ValidDate,y))
    --as unpvt
    
    
    --INSERT INTO #temptb  
    SELECT address0 as HDR,Address,name 
    into #temptb
    FROM 
    (select  Address0,Address,name from 
    (select EuipmentName,MAKEN01,ModelNo,ValidDate,y,name from @tb pvt) p
    unpivot
    (Address for Address0 in (EuipmentName,MAKEN01,ModelNo,ValidDate,y))
    as unpvt) tt
    
    
    DECLARE @SQLStatement NVARCHAR(MAX) = N'' --Variable to hold t-sql query
    DECLARE @UniqueCustomersToPivot NVARCHAR(MAX) = N'' --Variable to hold unique customers to be used in PIVOT clause
    DECLARE @PivotColumnsToSelect NVARCHAR(MAX) = N'' --Variable to hold pivot column names with alias to be used in SELECt clause
    --Extarct unique customer names with pivot formattings
    SELECT @UniqueCustomersToPivot = @UniqueCustomersToPivot + ', [' + COALESCE(name, '') + ']' FROM (SELECT DISTINCT name FROM #temptb)DT
    SELECT @UniqueCustomersToPivot = LTRIM(STUFF(@UniqueCustomersToPivot, 1, 1, '')) --Remove first comma and space
    --Generate column names to be put in SELECT list with NULL handling and aliases also
    SELECT @PivotColumnsToSelect = @PivotColumnsToSelect + ', ISNULL([' + COALESCE(name, '') + '], 0) AS [' + name + ']'
    FROM (SELECT DISTINCT name FROM #temptb)DT
    --Generate dynamic PIVOT query here
    SET @SQLStatement =
    N'SELECT HDR '
    + @PivotColumnsToSelect +
    '
    FROM #temptb
    PIVOT
    (
    MAX(Address) FOR
    name IN
    (' + @UniqueCustomersToPivot + ')
    ) AS PVT
    '
    --Execute the dynamic t-sql PIVOT query below
    EXEC (@SQLStatement)
    
    --drop table #temptb

    reference: http://www.sqlrelease.com/dynamic-pivot-query-in-sql-server 

    Best Regards,

    Chris

    Wednesday, January 4, 2017 8:55 AM
  • User-2145645956 posted

    Hi, 

    Chris Zhao

    thank you very much its working, i tried without pivot table and got result. but this solution is right solution.. thank you very much

    Thursday, January 5, 2017 5:16 AM