crompnk
asked on
Format column headers in SQL Server view for reporting
Hi,
I have a view in SQL Server that creates a pivoted result set. I need to re-format the title of the columns to split the title over 2 rows. The two additional column headers would be derived from the existing column name by splitting the string based on the '|' operator.
I realise this is really the presentation layer but I wanted to know if it is possible to use TSQL and the existing view to give the required format, or is this better formatted using say Python in the SQL Machine Learning Services?
Below is some tsql just to demonstrate the output of the existing view:
Below is an example of the required format:
I have a view in SQL Server that creates a pivoted result set. I need to re-format the title of the columns to split the title over 2 rows. The two additional column headers would be derived from the existing column name by splitting the string based on the '|' operator.
I realise this is really the presentation layer but I wanted to know if it is possible to use TSQL and the existing view to give the required format, or is this better formatted using say Python in the SQL Machine Learning Services?
Below is some tsql just to demonstrate the output of the existing view:
DECLARE @TempTable TABLE (
Parameter NVARCHAR(30)
,[ID01|20190720] FLOAT
,[ID01|20190721] FLOAT
,[ID01|20190722] FLOAT
,[ID01|20190723] FLOAT
)
INSERT INTO @TempTable
VALUES (
'pH'
,'5'
,'6'
,'6'
,'5'
)
INSERT INTO @TempTable
VALUES (
'Alkalinity'
,'166'
,'168'
,'168'
,'163'
)
SELECT *
FROM @TempTable
Below is an example of the required format:
ASKER
Thanks for the feedback, in the example I provided the only fixed column would be [Parameter], the other columns are dynamically created by the underlying pivot view, so the number can vary, but the format of the column name is the same.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Variable number of columns means you have to build dynamic SQL commands to split column names from INFORMATION_SCHEMA.COLUMNS
Could you post the results of the following query?
Open in new window