Dynamic column names

• Ches Weldishofer

  Mr or Mrs. 500

  Points: 512

  More actions

  October 30, 2006 at 4:29 pm

  #114903

  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

• BeerBeerBeer

  SSCarpal Tunnel

  Points: 4021

  More actions

  October 30, 2006 at 4:35 pm

  #668887

  Based on the "play" data you've supplied, can you give an example of expected output? Unfortunately I am unable to grasp what you want from the description alone.

  ---

  Regards,

  Steve

  Life without beer is no life at all

  All beer is good, some beers are just better than others
  

• Ches Weldishofer

  Mr or Mrs. 500

  Points: 512

  More actions

  October 30, 2006 at 6:32 pm

  #668907

  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)

• BeerBeerBeer

  SSCarpal Tunnel

  Points: 4021

  More actions

  October 30, 2006 at 7:28 pm

  #668917

  <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

  ---

  Regards,

  Steve

  Life without beer is no life at all

  All beer is good, some beers are just better than others
  

• Ches Weldishofer

  Mr or Mrs. 500

  Points: 512

  More actions

  November 1, 2006 at 12:56 pm

  #669317

  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

• BeerBeerBeer

  SSCarpal Tunnel

  Points: 4021

  More actions

  November 1, 2006 at 1:32 pm

  #669326

  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. 

  ---

  Regards,

  Steve

  Life without beer is no life at all

  All beer is good, some beers are just better than others
  

• Jeff Moden

  SSC Guru

  Points: 1004292

  More actions

  November 1, 2006 at 7:25 pm

  #669380

  Don't think you need a cursor... see the oversimplified example at the following...

  http://www.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)