Link to home
Start Free TrialLog in
Avatar of crompnk
crompnkFlag for United Kingdom of Great Britain and Northern Ireland

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:

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

Open in new window


Below is an example of the required format:
User generated image
Avatar of Pavel Celba
Pavel Celba
Flag of Czechia image

T-SQL allows many things today... Does your @TempTable have fixed number of columns or the number can vary?

Variable number of columns means you have to build dynamic SQL commands to split column names from INFORMATION_SCHEMA.COLUMNS  into this variable structure...

Could you post the results of the following query?
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'YourViewName'

Open in new window

Avatar of crompnk

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
Avatar of Pavel Celba
Pavel Celba
Flag of Czechia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial