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

Question

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?

To construct a result set comprised of dynamically generated columns, yes you will need dynamic sql; you won't be able to do this in a view however, it will need a stored procedure. You could have your procedure build a temporary or a permanent table for further use. — Stu, Jul 22 '21 at 20:38

John Cappelletti · Accepted Answer · 2021-07-22T21:04:38.093

2

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

edited Jul 22 '21 at 21:04

answered Jul 22 '21 at 20:53

John Cappelletti

79,615
7
44
66

I tried this approach but it doesn't like the fact that '#YourTable' in my case is itself a view, not a table and 'Parameter' is a column in that view. Do these objects inside of a dynamic sql statement need to reference an actual table? – Addison Waege Jul 22 '21 at 21:54
@AddisonWaege Change #YourTable to the actual VIEW name. The source can be a table or a view, it just doesn't matter. Just to be clear... dynamic SQL can NOT exists as or within a view. – John Cappelletti Jul 22 '21 at 22:00
Could you explain the reasoning for the 'sum(Value' clause in the query? In my actual database not all the values are of the same datatype so it is throwing an exception on this part. – Addison Waege Jul 22 '21 at 22:23
1

@AddisonWaege PIVOT requires an aggregation. Change sum() to max(). Based on your sample data, I assumed numeric. – John Cappelletti Jul 22 '21 at 22:33

paneerakbari · Answer 2 · 2021-07-22T20:51:51.470

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

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

2 Answers2