9

I have a table (not designed by me) which has 20 variably named columns. That is, depending on what type of record you are looking at, the applicable name of the column can change.

The possible column names are stored in another table, that I can query very easily.

Therefore, the query I'm really looking for goes something like this:

SELECT Col1 AS (SELECT ColName FROM Names WHERE ColNum = 1 and Type = @Type),
       Col2 AS (SELECT ColName FROM Names WHERE ColNum = 2 and Type = @Type)
FROM   Tbl1 
WHERE  Type = @Type

Obviously that doesn't work, so how can I get a similar result?

' I've tried building a query string and EXECUTEing it, but that just returns "Command(s) Completed Successfully" and doesn't seem to return a rowset. It turns out I was using an incorrect query to build the dynamic SQL and as such built an empty string. SQL Server definitely executed the empty string correctly.

Note that the reason I need this to occur, rather than simply hard coding the column names, is that the column names are user configurable.

  • 1
    What happens if you PRINT the query string, copy/paste into a new query window and execute it there? – DenisT Feb 17 '14 at 22:58
  • "User configurable" meaning there are hundreds or thousands of types, and/or aliases being changed frequently? If the aliases are fairly stable, I'd recommend creating a series of views. – Jon of All Trades Feb 17 '14 at 23:06
  • @DenisT, It doesn't output anything, which perhaps indicates something else is wrong as well. Thanks for the lead. – Hotchips Feb 17 '14 at 23:07
  • @JonofAllTrades Unfortunately, while they are fairly stable, it's very much part of the spec that when the user changes something in the software, that thing must also change in the reports. – Hotchips Feb 17 '14 at 23:08
  • @DenisT It turns out that my subqueries used to build the dynamic SQL were incorrect and returned null sets. So SQL Server returned a blank query, which it duly executed successfully. Thanks for pointing out the PRINT command. – Hotchips Feb 17 '14 at 23:25
12

Try the following code:

CREATE TABLE #Names
(
    [Type] VARCHAR(50),
    ColNum SMALLINT,
    ColName VARCHAR(50),
    ColDataType VARCHAR(20)
)

INSERT  INTO #Names VALUES
('Customer', 1, 'CustomerID', 'INT'),
('Customer', 2, 'CustomerName', 'VARCHAR(50)'),
('Customer', 3, 'CustomerJoinDate', 'DATE'),
('Customer', 4, 'CustomerBirthDate', 'DATE'),
('Account', 1, 'AccountID', 'INT'),
('Account', 2, 'AccountName', 'VARCHAR(50)'),
('Account', 3, 'AccountOpenDate', 'DATE'),
('CustomerAccount', 1, 'CustomerID', 'INT'),
('CustomerAccount', 2, 'AccountID', 'INT'),
('CustomerAccount', 3, 'RelationshipSequence', 'TINYINT')


CREATE TABLE #Data
(
    [Type] VARCHAR(50),
    Col1 VARCHAR(50),
    Col2 VARCHAR(50),
    Col3 VARCHAR(50),
    Col4 VARCHAR(50),
    Col5 VARCHAR(50),
    Col6 VARCHAR(50),
    Col7 VARCHAR(50)
)

INSERT  INTO #Data VALUES
('Customer', '1', 'Mr John Smith', '2005-05-20', '1980-11-15', NULL, NULL, NULL),
('Customer', '2', 'Mrs Hayley Jones', '2009-10-10', '1973-04-03', NULL, NULL, NULL),
('Customer', '3', 'ACME Manufacturing Ltd', '2012-12-01', NULL, NULL, NULL, NULL),
('Customer', '4', 'Mr Michael Crocker', '2014-01-13', '1957-01-23', NULL, NULL, NULL),
('Account', '1', 'Smith-Jones Cheque Acct', '2005-05-25', NULL, NULL, NULL, NULL),
('Account', '2', 'ACME Business Acct', '2012-12-01', NULL, NULL, NULL, NULL),
('Account', '3', 'ACME Social Club', '2013-02-10', NULL, NULL, NULL, NULL),
('Account', '4', 'Crocker Tipping Fund', '2014-01-14', NULL, NULL, NULL, NULL),
('CustomerAccount', '1', '1', '1', NULL, NULL, NULL, NULL),
('CustomerAccount', '2', '1', '2', NULL, NULL, NULL, NULL),
('CustomerAccount', '2', '3', '2', NULL, NULL, NULL, NULL),
('CustomerAccount', '3', '2', '1', NULL, NULL, NULL, NULL),
('CustomerAccount', '3', '3', '1', NULL, NULL, NULL, NULL),
('CustomerAccount', '4', '2', '2', NULL, NULL, NULL, NULL),
('CustomerAccount', '4', '4', '1', NULL, NULL, NULL, NULL)


DECLARE @Type VARCHAR(50) = 'Account' -- Or Customer, or CustomerAccount

DECLARE @SQLText NVARCHAR(MAX) = ''

SELECT  @SQLText += 'SELECT '

SELECT  @SQLText += ( -- Add in column list, with dynamic column names.
                SELECT  'CONVERT(' + ColDataType + ', Col' + CONVERT(VARCHAR, ColNum) + ') AS [' + ColName + '],'
                FROM    #Names
                WHERE   [Type] = @Type FOR XML PATH('')
            )

SELECT  @SQLText = LEFT(@SQLText, LEN(@SQLText) - 1) + ' ' -- Remove trailing comma

SELECT  @SQLText += 'FROM #Data WHERE [Type] = ''' + @Type + ''''

PRINT   @SQLText
EXEC    sp_executesql @SQLText

This returns the SELECT statement: SELECT CONVERT(INT, Col1) AS [AccountID],CONVERT(VARCHAR(50), Col2) AS [AccountName],CONVERT(DATE, Col3) AS [AccountOpenDate] FROM #Data WHERE [Type] = 'Account'

  • Using dynamic SQL is the correct answer, given that the question asked how to do it with SQL. It was also something I'd tried to do, but incorrectly. – Hotchips Feb 17 '14 at 23:27
  • Bear in mind that if you are accepting user input and using it to build dynamic SQL, then you really, really need to be concerned about SQL injection and sanitizing inputs. bobby-tables.com – Jonathan Van Matre Feb 18 '14 at 19:35
  • @JonathanVanMatre Absolutely. Fortunately, this is only for internal use, and all the inputs are already sanitized by the app. – Hotchips Feb 18 '14 at 22:21
7

This sounds prime for a front end display solution. Query 1 would pull back your data, Query 2 would pull back the column names and in code when you build what ever structure you use to display you set the headers from the second query.

While a Pure SQL Method may be possible it will be dynamic SQL and code maintnence would be a nightmare.

Also your probably looking for sp_executesql and not just EXECUTE N'Query String' as that may fix your issue of command completed successfully.

  • I agree, and I could definitely do this in SSRS, but I can't do it in the other reporting software I'm using at the moment. – Hotchips Feb 17 '14 at 23:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.