Sql: Dynamic View with Column Names Based on Column Values in Source Table

SQL: Dynamic view with column names based on column values in source table

You can perform this with a PIVOT. When doing the PIVOT you can do it one of two ways, with a Static Pivot that you will code the rows to transform or a Dynamic Pivot which will create the list of columns at run-time:

Static Pivot (See SQL Fiddle for Demo):

select id, [user], [engineer], [manu], [OS]
from 
(
    select t.id
        , t.[user]
        , p.ticketid
        , p.label
        , p.value
    from tickets t
    inner join properties p
        on t.id = p.ticketid
) x
pivot
(
    min(value)
    for label in ([engineer], [manu], [OS])
) p

Or you can use a Dynamic Pivot (See SQL Fiddle for Demo):

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(p.label) 
                    from tickets t
                    inner join properties p
                        on t.id = p.ticketid
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT id, [user], ' + @cols + ' from 
             (
                 select t.id
                        , t.[user]
                        , p.ticketid
                        , p.label
                        , p.value
                    from tickets t
                    inner join properties p
                        on t.id = p.ticketid
            ) x
            pivot 
            (
                min(value)
                for label in (' + @cols + ')
            ) p '

execute(@query)

Both query will return the same results.

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

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

Create dynamic select get value for column name - in SQL Server

This may help:

CREATE Table Device (
DeviceCode NVARCHAR(100) NOT NULL,
PartTypeName NVARCHAR(100) NOT NULL,
PartInfo NVARCHAR(100) NOT NULL
)

Insert Into Device
Values('VT.SX-01','CPU','Pentium G6650'),
('VT.SX-01','Motherboard','H81M -  S2PV'),
('VT.SX-01','RAM','DDR# 4GB - bus 1866 - Nano'),
('VT.SX-01','PartType Name 01','PartInfo 01')

--QUERY
DECLARE @DynamicPivotQuery AS NVARCHAR(MAX);
DECLARE @ColumnNamesInPivot AS NVARCHAR(MAX);

--Get distinct values of PIVOT Column 
SELECT   @ColumnNamesInPivot = ISNULL(@ColumnNamesInPivot + ',', '')
        + QUOTENAME([PartTypeName])
FROM    ( SELECT    DISTINCT
                    [PartTypeName]
          FROM      Device
        ) AS P

SELECT  @DynamicPivotQuery = N'Select DeviceCode,'
        + @ColumnNamesInPivot + ' 
            FROM    ( SELECT * 
          FROM      Device
        ) AS SourceTable PIVOT( MAX(PartInfo) FOR [PartTypeName] IN ('
        + @ColumnNamesInPivot + ') ) AS PVTTable'

EXEC sp_executesql @DynamicPivotQuery;

And the result will be:

SQL: Select dynamic column name based on variable

EXEC ('SELECT ''value'' AS ' + @myDynamicColumn)

Mysql select dynamic row values as column name from a table and value from another table

Finally the code is working

SET @sql = NULL;
    SELECT
    GROUP_CONCAT(DISTINCT
    CONCAT(
    'MAX(IF(a.ques_id = ',
    ques_id,
    ', a.ques_ans, NULL)) AS `',
    ques_name,'`'
    )
    ) INTO @sql
     FROM survey_answer inner join survey_question on  survey_answer.ques_id=survey_question.id;

set @sql = CONCAT('select u.user_name ,q.category_id,a.p_code, ' ,@sql,'  FROM `survey_answer` as a
LEFT JOIN `users` as u ON a.user_id = u.user_id
LEFT JOIN `survey_question` as q ON a.ques_id= q.id');
 PREPARE stmt FROM @sql;
 EXECUTE stmt;
 DEALLOCATE PREPARE stmt;

Dynamic SQL to generate column names?

Having answered a lot of these over the years by generating dynamic pivot SQL from the metadata, have a look at these examples:

SQL Dynamic Pivot - how to order columns

SQL Server 2005 Pivot on Unknown Number of Columns

What SQL query or view will show "dynamic columns"

How do I Pivot on an XML column's attributes in T-SQL

How to apply the DRY principle to SQL Statements that Pivot Months

In your particular case (using the ANSI pivot instead of SQL Server 2005's PIVOT feature):

DECLARE @template AS varchar(max)
SET @template = 'SELECT 
SKU1
{COLUMN_LIST}
FROM
OrderDetailDeliveryReview
Group By
OrderShipToID,
DeliveryDate,
SKU1
'

DECLARE @column_list AS varchar(max)
SELECT @column_list = COALESCE(@column_list, ',') + 'SUM(Case When Sku2=' + CONVERT(varchar, Sku2) + ' Then Quantity Else 0 End) As [' + CONVERT(varchar, Sku2) + '],' 
FROM OrderDetailDeliveryReview
GROUP BY Sku2
ORDER BY Sku2

Set @column_list = Left(@column_list,Len(@column_list)-1)

SET @template = REPLACE(@template, '{COLUMN_LIST}', @column_list)

EXEC (@template)

Stored procedure to create view with dynamic ID based on another table

Sounds to me like Row_Number() is what you are looking for. Try using this to create your view.

CREATE view myView AS
SELECT Row_Number() OVER(ORDER BY ID) ID, Name
FROM dbo.table_1

Dynamically Build SQL Query based on table and column information stored in a Table

try this,

DECLARE @TEMP_TABLE TABLE
(
    table_ID INT,
    predicateName VARCHAR(250),
    EntityName VARCHAR(250)
)

INSERT INTO @TEMP_TABLE
(
    table_ID,predicateName,EntityName
)
SELECT 1,'AGE','tbl_PR_AGE_VALIDATION_RULE' UNION ALL
SELECT 1,'SALARY','tbl_PR_AGE_VALIDATION_RULE' UNION ALL
SELECT 1,'GENDER','tbl_PR_AGE_VALIDATION_RULE' UNION ALL
SELECT 1,'DATEOBIRTH','tbl_PR_AGE_VALIDATION_RULE' UNION ALL
SELECT 1,'CITY','tbl_PR_AGE_VALIDATION_RULE' UNION ALL
SELECT 2,'TEST1','tbl_PR_PAGE_VALIDATION_RULE' UNION ALL
SELECT 3,'TEST2','tbl_PR_CITY_VALIDATION'

DECLARE @VC_COLUMN_LIST VARCHAR(2000)='',@VC_TABLE_LIST VARCHAR(2000)=''

SELECT  @VC_COLUMN_LIST+=EntityName+'.'+predicateName+','+CHAR(10)
FROM @TEMP_TABLE

SELECT @VC_COLUMN_LIST=LEFT(@VC_COLUMN_LIST,LEN(@VC_COLUMN_LIST)-2)

;WITH CTE_TEMP AS
(
        SELECT DISTINCT table_ID, EntityName
        FROM @TEMP_TABLE
)
SELECT @VC_TABLE_LIST+=IIF(B.table_ID IS NULL,'','LEFT JOIN ')+A.EntityName+IIF(B.table_ID IS NULL,'',' ON '+A.EntityName+'.ENTITY_ID = '+B.EntityName+'.ENTITY_ID')+CHAR(10) 
FROM CTE_TEMP A
LEFT JOIN CTE_TEMP B ON A.table_ID=B.table_ID+1 

PRINT
(
    'SELECT '+@VC_COLUMN_LIST+CHAR(10)+
    'FROM '+@VC_TABLE_LIST
)

---

Related Topics