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

  • 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

  • Sure. If someone had the UK set as their preference they'd get this....

     

    Business UnitJanuaryQuarter 1EmployeeID
    ---------------------------------------------------

    UK Business UnitUK JanuaryUK Quarter 1UK EmployeeID
    (1 row(s) affected)

    Though someone from France would get this.

    Business UnitJanuaryQuarter 1EmployeeID
    ---------------------------------------------------

    FR Business UnitFR JanuaryFR Quarter 1FR 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


    Regards,

    Steve

    Life without beer is no life at all

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

  • 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. 


    Regards,

    Steve

    Life without beer is no life at all

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

  • 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)

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply