SQL view with dynamic count of columns

Question

I know it is impossible directly, but maybe with help of sql functions it is possible to create view with dynamic column count? What exactly I want to do is - Create view which's columns would be username (first table's values), date (second table's values), and multiple columns for properties (each third table's row would be column).User table structure - ID and username, Date table structure - ID and datetime, Property table structure - ID, property name, property value, fk user ID (plus one table to relate tables). Result should look something like that (columns):

Can anyone could show any simple sample ?

You can create a view with an every number of columns you want. But once you've created the view the set of columns will remain unchanged until the view will be recreated, because of relation nature of RDBMS. — max, Feb 24 '14 at 14:45
possible duplicate of [Dynamic Pivot Columns in SQL Server](http://stackoverflow.com/questions/14797691/dynamic-pivot-columns-in-sql-server) — Olivier Jacot-Descombes, Feb 24 '14 at 14:57

score 2 · Answer 1 · answered Feb 24 '14 at 14:48

This is not possible in standard SQL, nor in any version of SQL that I am familiar with (SQL Server, Oracle, MySql, Access SQL). Tables, Expressions and Views in SQL have a fixed column-set, by design. I.E., it's intentionally restricted this way. AFAIK, in most versions of SQL, Stored Procedures are the only objects that can return a variable column-set.

score 0 · Answer 2 · answered Feb 24 '14 at 14:55

I've made some assumptions about your data schema but it looks like you're trying to generate something like this

CREATE VIEW User_Properties AS
SELECT 
[username], [date]
,MAX(CASE WHEN [PropertyName] = 'property 1' THEN [PropertyValue]  ELSE NULL END) AS [property 1]
,MAX(CASE WHEN [PropertyName] = 'property 2' THEN [PropertyValue]  ELSE NULL END) AS [property 2]
,MAX(CASE WHEN [PropertyName] = 'property 3' THEN [PropertyValue]  ELSE NULL END) AS [property 3]
....
,MAX(CASE WHEN [PropertyName] = 'property n' THEN [PropertyValue]  ELSE NULL END) AS [property n]
GROUP BY [username], [date]

Which can be automated along the lines of

--Cursor to return the list of Properties
DECLARE PROPERTY_CURSOR CURSOR FOR SELECT PropertyName From UserPropertyTable

DECLARE @PropertyName nvarchar(255)
DECLARE @SQL nvarchar(max)

--Start Building the view definition
SET @SQL ='CREATE VIEW User_Properties AS
SELECT [username], [date]'

--Add a column for each Property
OPEN PROPERTY_CURSOR 
FETCH NEXT FROM PROPERTY_CURSOR INTO @PropertyName
WHILE @@FETCH_STATUS =0
BEGIN
SET @SQL = ',MAX(CASE WHEN [PropertyName] = '+QUOTENAME(@PropertyName,'''')+' THEN [PropertyValue]  ELSE NULL END) AS '+QUOTENAME(@PropertyName)
FETCH NEXT FROM PROPERTY_CURSOR INTO @PropertyName
END
DEALLOCATE PROPERTY_CURSOR 


--Finish off the Create_View SQL 
SET @SQL =@SQL +'
FROM UserPropertyTable
GROUP BY [username], [date]'

--Execute the Create_View SQL 
EXEC (@SQL)

SQL view with dynamic count of columns

2 Answers2

Linked

Related