Dynamic column names
-
I'm looking for a T-SQL Solution to create dynamic fieldnames and values within a query. I've put some play schema and data below for the adventurous ones.
Our goal is to have a translation table to pull column headings for RS reports out of based on the value stored for the individual running the report in a preferences table. Using a recordset that has the headername as a column name and the header value as it's value....
in this statement
select
* from tHeaders H join tHeaderTranslate T on H.HdrID = T.HdrID join tUserPreferences P on T.LngID = P.LngID where P.UserID = 4
I'd really like to see only 1 record returned with the Column headings of H.Header and the values of T.Heading.
Now that I've confused me and half the known world. Any suggestion would be appreciated.
Schema and test data:
CREATE TABLE [dbo].[tHeaders]
(
[HdrID] [int]
NOT NULL IDENTITY(1, 1),
[Header] [varchar]
(50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
)
ON [PRIMARY]
GO
ALTER
TABLE [dbo].[tHeaders] ADD CONSTRAINT [PK_tHeader] PRIMARY KEY CLUSTERED ([HdrID]) ON [PRIMARY]
GO
CREATE
TABLE [dbo].[tHeaderTranslate]
(
[HdrTrnsID] [int]
NOT NULL IDENTITY(1, 1),
[HdrID] [int]
NOT NULL,
[LngID] [int]
NOT NULL,
[Heading] [varchar]
(50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
)
ON [PRIMARY]
GO
ALTER
TABLE [dbo].[tHeaderTranslate] ADD CONSTRAINT [PK_tHeaderTranslate] PRIMARY KEY CLUSTERED ([HdrID], [LngID]) ON [PRIMARY]
GO
CREATE
TABLE [dbo].[tLanguages]
(
[LngID] [int]
NOT NULL IDENTITY(1, 1),
[LNG] [varchar]
(50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
)
ON [PRIMARY]
GO
ALTER
TABLE [dbo].[tLanguages] ADD CONSTRAINT [PK_tLanguages] PRIMARY KEY CLUSTERED ([LngID]) ON [PRIMARY]
GO
CREATE
TABLE [dbo].[tUserPreferences]
(
[UserID] [int]
NOT NULL IDENTITY(1, 1),
[Name] [nchar]
(10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[LngID] [int]
NOT NULL
)
ON [PRIMARY]
GO
ALTER
TABLE [dbo].[tUserPreferences] ADD CONSTRAINT [PK_tUserPreferences] PRIMARY KEY CLUSTERED ([UserID]) ON [PRIMARY]
GO
SET
IDENTITY_INSERT [dbo].[tUserPreferences] ON
INSERT
INTO [dbo].[tUserPreferences] ([UserID], [Name], [LngID]) VALUES (1, N'US User ', 1)
INSERT
INTO [dbo].[tUserPreferences] ([UserID], [Name], [LngID]) VALUES (2, N'UK User ', 2)
INSERT
INTO [dbo].[tUserPreferences] ([UserID], [Name], [LngID]) VALUES (3, N'Fr User ', 3)
INSERT
INTO [dbo].[tUserPreferences] ([UserID], [Name], [LngID]) VALUES (4, N'Sp User ', 4)
SET
IDENTITY_INSERT [dbo].[tUserPreferences] OFF
SET
IDENTITY_INSERT [dbo].[tHeaders] ON
INSERT
INTO [dbo].[tHeaders] ([HdrID], [Header]) VALUES (1, N'Business Unit')
INSERT
INTO [dbo].[tHeaders] ([HdrID], [Header]) VALUES (2, N'January')
INSERT
INTO [dbo].[tHeaders] ([HdrID], [Header]) VALUES (3, N'Quarter 1')
INSERT
INTO [dbo].[tHeaders] ([HdrID], [Header]) VALUES (4, N'Employee ID')
SET
IDENTITY_INSERT [dbo].[tHeaders] OFF
SET
IDENTITY_INSERT [dbo].[tHeaderTranslate] ON
INSERT
INTO [dbo].[tHeaderTranslate] ([HdrID], [LngID], [HdrTrnsID], [Heading]) VALUES (1, 1, 1, N'Eng Business Unit')
INSERT
INTO [dbo].[tHeaderTranslate] ([HdrID], [LngID], [HdrTrnsID], [Heading]) VALUES (1, 2, 2, N'UK Business Unit')
INSERT
INTO [dbo].[tHeaderTranslate] ([HdrID], [LngID], [HdrTrnsID], [Heading]) VALUES (1, 3, 3, N'Fr Business Unit')
INSERT
INTO [dbo].[tHeaderTranslate] ([HdrID], [LngID], [HdrTrnsID], [Heading]) VALUES (1, 4, 4, N'Sp Business Unit')
INSERT
INTO [dbo].[tHeaderTranslate] ([HdrID], [LngID], [HdrTrnsID], [Heading]) VALUES (2, 1, 5, N'Eng January')
INSERT
INTO [dbo].[tHeaderTranslate] ([HdrID], [LngID], [HdrTrnsID], [Heading]) VALUES (2, 2, 6, N'UK January')
INSERT
INTO [dbo].[tHeaderTranslate] ([HdrID], [LngID], [HdrTrnsID], [Heading]) VALUES (2, 3, 7, N'Fr January')
INSERT
INTO [dbo].[tHeaderTranslate] ([HdrID], [LngID], [HdrTrnsID], [Heading]) VALUES (2, 4, 8, N'Sp January')
INSERT
INTO [dbo].[tHeaderTranslate] ([HdrID], [LngID], [HdrTrnsID], [Heading]) VALUES (3, 1, 9, N'Eng Quarter 1')
INSERT
INTO [dbo].[tHeaderTranslate] ([HdrID], [LngID], [HdrTrnsID], [Heading]) VALUES (3, 2, 10, N'UK Quarter 1')
INSERT
INTO [dbo].[tHeaderTranslate] ([HdrID], [LngID], [HdrTrnsID], [Heading]) VALUES (3, 3, 11, N'Fr Quarter 1')
INSERT
INTO [dbo].[tHeaderTranslate] ([HdrID], [LngID], [HdrTrnsID], [Heading]) VALUES (3, 4, 12, N'Sp Quarter 1')
INSERT
INTO [dbo].[tHeaderTranslate] ([HdrID], [LngID], [HdrTrnsID], [Heading]) VALUES (4, 1, 13, N'Eng Employee ID')
INSERT
INTO [dbo].[tHeaderTranslate] ([HdrID], [LngID], [HdrTrnsID], [Heading]) VALUES (4, 2, 14, N'UK Employee ID')
INSERT
INTO [dbo].[tHeaderTranslate] ([HdrID], [LngID], [HdrTrnsID], [Heading]) VALUES (4, 3, 15, N'Fr Employee ID')
INSERT
INTO [dbo].[tHeaderTranslate] ([HdrID], [LngID], [HdrTrnsID], [Heading]) VALUES (4, 4, 16, N'Sp Employee ID')
SET
IDENTITY_INSERT [dbo].[tHeaderTranslate] OFF
SET
IDENTITY_INSERT [dbo].[tLanguages] ON
INSERT
INTO [dbo].[tLanguages] ([LngID], [LNG]) VALUES (1, N'en-US')
INSERT
INTO [dbo].[tLanguages] ([LngID], [LNG]) VALUES (2, N'en-UK')
INSERT
INTO [dbo].[tLanguages] ([LngID], [LNG]) VALUES (3, N'fr-BE')
INSERT
INTO [dbo].[tLanguages] ([LngID], [LNG]) VALUES (4, N'sp-PR')
SET
IDENTITY_INSERT [dbo].[tLanguages] OFF
-
Sure. If someone had the UK set as their preference they'd get this....
Business Unit January Quarter 1 EmployeeID ---------------- ---------- ------------ ------------- UK Business Unit UK January UK Quarter 1 UK EmployeeID (1 row(s) affected) Though someone from France would get this.
Business Unit January Quarter 1 EmployeeID ---------------- ---------- ------------ ------------- FR Business Unit FR January FR Quarter 1 FR EmployeeID (1 row(s) affected) -
<P>It may not be the most elegant, but it will work and you can extend your columns as well</P><PRE>
declare
@sql VARCHAR(8000)
, @Select VARCHAR(8000)
, @From VARCHAR(8000)
, @Where VARCHAR(8000)
select @Select = ' SELECT '
select @From = ' FROM tLanguages l INNER JOIN '
select @Where = ' WHERE '
DECLARE
curHeader CURSOR
READ_ONLY
FOR
select hdrid, Header from tHeaders order by hdrid
DECLARE
@HdrId INT
, @Header varchar(40)
OPEN
curHeader
FETCH
NEXT FROM curHeader INTO @HdrId, @Header
WHILE
(@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
-- PRINT 'add user defined code here'
-- eg.
IF (@HdrId = 1)
BEGIN
SELECT @Select = @Select + '[' + @Header + '] = t' + CONVERT(VARCHAR, @HdrId) + '.Heading
'
SELECT @From = @From + ' tHeaderTranslate t' + CONVERT(VARCHAR, @HdrId) + '
ON
t'
+ CONVERT(VARCHAR, @HdrId) + '.lngid = l.lngid
'
SELECT @Where = @Where + 't' + CONVERT(VARCHAR, @HdrId) + '.hdrid = ' + CONVERT(VARCHAR, @HdrId) + '
'
END
ELSE
BEGIN
SELECT @Select = @Select + ', [' + @Header + '] = t' + CONVERT(VARCHAR, @HdrId) + '.Heading
'
SELECT @From = @From + ' INNER JOIN tHeaderTranslate t' + CONVERT(VARCHAR, @HdrId) + '
ON
t'
+ CONVERT(VARCHAR, @HdrId) + '.lngid = l.lngid
'
SELECT @Where = @Where + ' AND t' + CONVERT(VARCHAR, @HdrId) + '.hdrid = ' + CONVERT(VARCHAR, @HdrId) + '
'
END
END
FETCH NEXT FROM curHeader INTO @HdrId, @Header
END
CLOSE
curHeader
DEALLOCATE
curHeader
SELECT
@sql = @Select + @From + @Where
print
@sql
exec
(@sql)
</PRE>
You might want to put some formatting around it as I always lose this when inserting code.
All you have left to do is determine how to pass the user preferences into the query (I wold suggest a stored procedure). Had to leave you something to do
-
Thank you Steve. I added a join to the user preference table to narrow down the results and it's working beautifully. I had hoped that somehow the PIVOT option in 2005 would make a cursor unneccessary, but se la vie.
Ches Weldishofer
-
I'm not totally familiar with the PIVOT function but I believe that you still have to know what data you're dealing with (which rows you want to turn to columns), so it is not totally flexible.
With the code example I have given you, all you would need to do is add another row for your headers and it still works - although you might need to think about LEFT JOINS so you still get a header when there is no value - this way you are not hiding the fact that there is no value. If you see what I mean.
-
Don't think you need a cursor... see the oversimplified example at the following...
http://qa.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=319791
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply