1

How can this query be written dynamically without declaring a variable for the categories so it would be possible to make an view?

This is a rephrase of my last question with a reproducible example:

CREATE TABLE dbo.tableName 
(
    category    varchar(300),
    product varchar(300),
    date_time   varchar(300),
    end_value   varchar(300)
);

INSERT INTO tableName (category,product,date_time,end_value) VALUES ('Site Furnishings', 'RX', '09/01/2021', '1,87');
INSERT INTO tableName (category,product,date_time,end_value) VALUES ('Site Furnishings', 'RX', '10/01/2021', '29,83');
INSERT INTO tableName (category,product,date_time,end_value) VALUES ('Site Furnishings', 'RX', '13/01/2021', '1,12');
INSERT INTO tableName (category,product,date_time,end_value) VALUES ('Site Furnishings', 'RX', '14/01/2021', '52,99');
INSERT INTO tableName (category,product,date_time,end_value) VALUES ('Site Furnishings', 'RX', '26/01/2021', '0,73');
INSERT INTO tableName (category,product,date_time,end_value) VALUES ('Granite Surfaces', 'V50', '09/01/2021', '1,13');
INSERT INTO tableName (category,product,date_time,end_value) VALUES ('Granite Surfaces', 'V50', '10/01/2021', '25,72');
INSERT INTO tableName (category,product,date_time,end_value) VALUES ('Granite Surfaces', 'V50', '26/01/2021', '3,95');
INSERT INTO tableName (category,product,date_time,end_value) VALUES ('Granite Surfaces', 'V50', '14/02/2021', '2,43');
INSERT INTO tableName (category,product,date_time,end_value) VALUES ('Glass & Glazing', 'Jetta', '09/01/2021', '92,65');
INSERT INTO tableName (category,product,date_time,end_value) VALUES ('Glass & Glazing', 'Jetta', '17/01/2021', '1,19');
INSERT INTO tableName (category,product,date_time,end_value) VALUES ('Glass & Glazing', 'Jetta', '18/01/2021', '1,19');

SELECT STUFF(
(SELECT DISTINCT ',' + QUOTENAME(category)
FROM dbo.tablename
FOR XML PATH (''), 
TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'')

Results in this: [Glass & Glazing],[Granite Surfaces],[Site Furnishings] which I use for my next query:

SELECT date_time, [Glass & Glazing],[Granite Surfaces],[Site Furnishings]
FROM (
    SELECT date_time, end_value, category
    FROM dbo.tablename
) x 
pivot 
( 
    max(end_value) 
    for category in ([Glass & Glazing],[Granite Surfaces],[Site Furnishings])
) p

How can this query be written dynamically without declaring a variable for the columns so it would be possible to make an view?

Thom A
  • 88,727
  • 11
  • 45
  • 75
random_data_enthutiast
  • 13
  • 3
  • As you tagged [[tag:azure]] (which I can't see has any relation to your question), I *assume* you are actually using Azure SQL Database? If so, why are you using `FOR XML PATH` for string aggregation, and not `STRING_AGG`? – Thom A Sep 05 '22 at 14:30
  • Does this answer your question? [SQL Server dynamic PIVOT query?](https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) – Thom A Sep 05 '22 at 14:30
  • Thanks for the update @Larnu. Yes I use Azure SQL. No it does not. It uses a dynamic query to execute as a stored procedure, however, I want this stored as a view. – random_data_enthutiast Sep 05 '22 at 14:38
  • *"I want this stored as a view."* You *can't*. A `VIEW`'s definition must be strictly defined. You could use a stored procedure, but you *cannot* have a dynamic dataset returned by a `VIEW`. – Thom A Sep 05 '22 at 14:39

1 Answers1

0

Basically you cannot. Views are static, think of them as virtual tables. You can't do dynamic stuff in them. That has to go in a SPROC.

planetmatt
  • 407
  • 3
  • 10