CODEWITHSUNDEEP
sql-serverdynamic-sql
Addison Waege
Addison Waege

Reputation: 49

SQL Server : using column values as table headers in a view

I currently have a table in SQL Server that looks something like this:

Machine Serial Date Parameter Value
Machine 1 12345 7/22/2021 Param 1 789
Machine 1 12345 7/22/2021 Param 2 456
Machine 1 67890 7/22/2021 Param 1 123
Machine 1 67890 7/22/2021 Param 2 456
Machine 1 34567 7/22/2021 Param 1 789
Machine 1 34567 7/22/2021 Param 3 123

I am attempting to create a view that transforms this table into something like this, where each row is defined by it's serial number and the view headers are defined dynamically by the parameters in the 'Parameter' column

Serial Machine Date Param 1 Param 2 Param 3
12345 Machine 1 7/22/2021 789 456
67890 Machine 1 7/22/2021 123 456
34567 Machine 1 7/22/2021 789 123

The intention here is that the original table can contain any number of unique parameters and the resultant view will then group by serial number and populate each column with it's respective value based on the parameter it is assigned.

So far I've tried messing with dynamic SQL but have not been able to produce any results. I have done similar things in the past without a dynamic number of parameters in the parameter column. Is something like this even possible on the database side or does this kind of manipulation need to happen on the client side?

Upvotes: 0

Views: 2464

Answers (2)

John Cappelletti
John Cappelletti

Reputation: 82020

If you want to perform this in SQL Server and not the presentation layer, Dynamic SQL would be required

Example

Declare @SQL varchar(max) = '
Select *
From (
        Select Serial
              ,Machine
              ,Date
              ,Parameter
              ,Value
        From #YourTable
     ) src
 Pivot (sum(Value) For [Parameter] in (' + stuff((Select Distinct ',' + QuoteName(Parameter) From  #YourTable Order By 1 For XML Path('') ),1,1,'')  + ') ) pvt'
Exec(@SQL);

Results

enter image description here

Upvotes: 2

paneerakbari
paneerakbari

Reputation: 725

One approach would be to use CASE statements on additional columns in your view definition like this:

SELECT Serial,
       Machine,
       Date,
       CASE WHEN Parameter = 'Param1' THEN Value ELSE '' END AS Param1,
       CASE WHEN Parameter = 'Param2' THEN Value ELSE '' END AS Param2,
       CASE WHEN Parameter = 'Param3' THEN Value ELSE '' END AS Param3

This won't scale well to a large number of parameters, but it should work fine for what you've described here.

If you need this to be dynamic or to accommodate more values you should look into using a PIVOT

Edit: Adding that approach as well, for completeness.

DROP TABLE IF EXISTS #pivot;

CREATE TABLE #pivot
 (Serial INT,
  Machine VARCHAR(20),
  [Date] DATETIME,
  Parameter VARCHAR(6),
  VALUE INT)

INSERT INTO #pivot
VALUES (12345, 'Machine 1', GETDATE(), 'Param1', 789),
       (12345, 'Machine 1', GETDATE(), 'Param2', 456),
       (67890, 'Machine 1', GETDATE(), 'Param1', 789),
       (67890, 'Machine 1', GETDATE(), 'Param2', 456),
       (34567, 'Machine 1', GETDATE(), 'Param1', 789),
       (34567, 'Machine 1', GETDATE(), 'Param3', 123);

SELECT *
  FROM #pivot;


SELECT Serial, Machine, Date, Param1, Param2, Param3
  FROM #pivot AS p
 PIVOT (
    SUM(p.Value)
    FOR p.Parameter
    IN (Param1, Param2, Param3)
 ) pvt

https://learn.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-ver15

Upvotes: 0

Related Questions