Dynamically outputting a set of columns based on another Tables records

Question

Dynamically outputting a set of columns based on another Tables records

Weegee2017

SSC Eights!

Points: 994
More actions
January 25, 2019 at 9:37 am

#382644

I have a table (called #OutputTable) which i would like to be able to dynamically select certain columns from
For example Col1, Col56, col57, col58, col59
I have another table which specifies the columns , that I want to select out of #OutputTable
and this is called #FileImportTypeColumn
#FileImportTypeColumn lists out all the columns in #OutputTable
How can i query #FileImportTypeColumn (Using column Sequence) to dynamically
select the ColumnNames and use these in my Select Statement for #OutputTable?
I tried writing this pivot query on #FileImportColumn to retrieve the columns
but couldnt get very far
SELECT *
FROM
  (SELECT
   FileImportTypeID, ColumnName,ColumnSequence
  FROM
   [#FileImportTypeColumn]
  where FileImportTypeID = 30100
     ) P
PIVOT (max(ColumnName) FOR ColumnSequence IN ([1],[56],[57],[58],[59],[60],[61])
     ) AS PVT
The ddl scripts used to create the 2 tables are attached (in text format)

Viewing 15 posts - 1 through 14 (of 14 total)

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

Thom A SSC Guru Points: 99130 More actions · Answer 1

I'm not sure I actually understand your goal here I'm afraid. What are you expected results and from what data are you expecting to get that data from? If you want to dynamically select tables, you have to use dynamic SQL; of which you haven't included any of in your question which makes me think that it's not a dynamic solution you're after.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Weegee2017 SSC Eights! Points: 994 More actions · Answer 2

Thom A - Friday, January 25, 2019 9:48 AM
I'm not sure I actually understand your goal here I'm afraid. What are you expected results and from what data are you expecting to get that data from? If you want to dynamically select tables, you have to use dynamic SQL; of which you haven't included any of in your question which makes me think that it's not a dynamic solution you're after.

Hi

This is the resultant output i want

select col1, col56, col57,col58,col59 from [#OutputTable]

where the columns selected by this statement

are determined by this

select columnSequence from [#FileImportTypeColumn]

Phil Parkin SSC Guru Points: 246960 More actions · Answer 3

What does the output from

select columnSequence from [#FileImportTypeColumn]

look like? If it's a column of data, should we assume that the order of the returned values is not important?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

jcelko212 32090 SSCrazy Eights Points: 9303 More actions · Answer 4

Weegee2017 - Friday, January 25, 2019 9:57 AM

Thom A - Friday, January 25, 2019 9:48 AM

This is not how you're supposed to use SQL. You're mixing data and metadata in the same schema, and using one table to drive the other table to determine what it means. This is a version of either the EAV design flaw or perhaps "squids automobiles and Lady Gaga" table design.

You are also rude because you didn't post DDL and made is actually leave the website to dangerously load files. Do you often load files from people you don't know? I hope not!

Please post DDL and follow ANSI/ISO standards when asking for help.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 5

jcelko212 32090 - Friday, January 25, 2019 1:39 PM

Weegee2017 - Friday, January 25, 2019 9:57 AM

Thom A - Friday, January 25, 2019 9:48 AM
This is not how you're supposed to use SQL. You're mixing data and metadata in the same schema, and using one table to drive the other table to determine what it means. This is a version of either the EAV design flaw or perhaps "squids automobiles and Lady Gaga" table design.
You are also rude because you didn't post DDL and made is actually leave the website to dangerously load files. Do you often load files from people you don't know? I hope not!

Attached text files are fine, Joe. They're better than trying to copy and paste long code. I do agree that linking to a different website to get files would suck.

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

Thom A SSC Guru Points: 99130 More actions · Answer 6

If I'm understanding correctly, the first mistake you are making is actually pivoting your data from #FileImportTypeColumn. That actually makes things a lot harder. If you return it as a row dataset you can easily construct some dynamic SQL:

DECLARE @SQL nvarchar(MAX);
SET @SQL = N'SELECT ' + STUFF((SELECT N',' + NCHAR(10) +
              N'   ' + QUOTENAME(CONCAT(N'Col', ColumnSequence)) + N' AS ' + QUOTENAME(ColumnName)
            FROM #FileImportTypeColumn
            WHERE FileImportTypeID = 30100 --This can be parametrised if needed
             ORDER BY ColumnSequence
            FOR XML PATH(N'')),1,9,N'') + NCHAR(10) +
      N'FROM #OutputTable;'
PRINT @SQL;
EXEC sp_executesql @SQL;

This outputs the following SQL:

SELECT [Col1] AS [AccountNumber],
   [Col56] AS [SHAREBALANCE],
    [Col57] AS [NewTakedaShares],
    [Col58] AS [STERLINGDUESCHEMECASH ],
    [Col59] AS [FRACTIONALGBPDUE],
    [Col60] AS [TOTAL GBP DUE SchemeFraction],
    [Col61] AS [GBPBACSCHANNELSchemeFraction]
FROM #OutputTable;

Hopefully, that is what you are after.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Weegee2017 SSC Eights! Points: 994 More actions · Answer 7

Thom A - Monday, January 28, 2019 4:08 AM
If I'm understanding correctly, the first mistake you are making is actually pivoting your data from #FileImportTypeColumn. That actually makes things a lot harder. If you return it as a row dataset you can easily construct some dynamic SQL:
DECLARE @SQL nvarchar(MAX);
SET @SQL = N'SELECT ' + STUFF((SELECT N',' + NCHAR(10) +
              N'   ' + QUOTENAME(CONCAT(N'Col', ColumnSequence)) + N' AS ' + QUOTENAME(ColumnName)
           FROM #FileImportTypeColumn
           WHERE FileImportTypeID = 30100 --This can be parametrised if needed
            ORDER BY ColumnSequence
           FOR XML PATH(N'')),1,9,N'') + NCHAR(10) +
   N'FROM #OutputTable;'
PRINT @SQL;
EXEC sp_executesql @SQL;
This outputs the following SQL:
SELECT [Col1] AS [AccountNumber],
   [Col56] AS [SHAREBALANCE],
   [Col57] AS [NewTakedaShares],
   [Col58] AS [STERLINGDUESCHEMECASH ],
   [Col59] AS [FRACTIONALGBPDUE],
   [Col60] AS [TOTAL GBP DUE SchemeFraction],
   [Col61] AS [GBPBACSCHANNELSchemeFraction]
FROM #OutputTable;
Hopefully, that is what you are after.

Hi Thom

When I run that , i get the following output

SELECT 1] AS [AccountNumber],

[Col56] AS [SHAREBALANCE],

[Col57] AS [NewTakedaShares],

[Col58] AS [STERLINGDUESCHEMECASH ],

[Col59] AS [FRACTIONALGBPDUE],

[Col60] AS [TOTAL GBP DUE SchemeFraction],

[Col61] AS [GBPBACSCHANNELSchemeFraction]

FROM #OutputTable;

Msg 102, Level 15, State 1, Line 30

Incorrect syntax near ']'

Thom A SSC Guru Points: 99130 More actions · Answer 8

Weegee2017 - Monday, January 28, 2019 4:31 AM
Thom A - Monday, January 28, 2019 4:08 AM
If I'm understanding correctly, the first mistake you are making is actually pivoting your data from #FileImportTypeColumn. That actually makes things a lot harder. If you return it as a row dataset you can easily construct some dynamic SQL:
DECLARE @SQL nvarchar(MAX);
SET @SQL = N'SELECT ' + STUFF((SELECT N',' + NCHAR(10) +
              N'   ' + QUOTENAME(CONCAT(N'Col', ColumnSequence)) + N' AS ' + QUOTENAME(ColumnName)
           FROM #FileImportTypeColumn
           WHERE FileImportTypeID = 30100 --This can be parametrised if needed
            ORDER BY ColumnSequence
           FOR XML PATH(N'')),1,9,N'') + NCHAR(10) +
   N'FROM #OutputTable;'
PRINT @SQL;
EXEC sp_executesql @SQL;
This outputs the following SQL:
SELECT [Col1] AS [AccountNumber],
   [Col56] AS [SHAREBALANCE],
   [Col57] AS [NewTakedaShares],
   [Col58] AS [STERLINGDUESCHEMECASH ],
   [Col59] AS [FRACTIONALGBPDUE],
   [Col60] AS [TOTAL GBP DUE SchemeFraction],
   [Col61] AS [GBPBACSCHANNELSchemeFraction]
FROM #OutputTable;
Hopefully, that is what you are after.
Hi Thom
When I run that , i get the following output
SELECT 1] AS [AccountNumber],
[Col56] AS [SHAREBALANCE],
[Col57] AS [NewTakedaShares],
[Col58] AS [STERLINGDUESCHEMECASH ],
[Col59] AS [FRACTIONALGBPDUE],
[Col60] AS [TOTAL GBP DUE SchemeFraction],
[Col61] AS [GBPBACSCHANNELSchemeFraction]
FROM #OutputTable;
Msg 102, Level 15, State 1, Line 30
Incorrect syntax near ']'

This is why I HATE the pasting on SSC... -_- It got rid of some of the spaces in the whitespace before the first QUOTENAME so it didn't have the correct amount (so STUFF removed more than just the formatting white space)... /sigh. I've attached a txt file.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Weegee2017 SSC Eights! Points: 994 More actions · Answer 9

Weegee2017

SSC Eights!

Points: 994

January 28, 2019 at 4:58 am

#2020258

Thats great. Thank you very much Thom

Thom A SSC Guru Points: 99130 More actions · Answer 10

Weegee2017 - Monday, January 28, 2019 4:58 AM
Thats great. Thank you very much Thom

Do you understand how it works however? The fact that you didn't know how to fix the prior problem does imply a no. If you don't, then it's important you do; it's your job to support the code you use (not myself, or any other users on SSC). If you need to understand please do ask about it and I'll be happy to explain.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Weegee2017 SSC Eights! Points: 994 More actions · Answer 11

Thom A - Monday, January 28, 2019 5:05 AM
Weegee2017 - Monday, January 28, 2019 4:58 AM
Thats great. Thank you very much Thom
Do you understand how it works however? The fact that you didn't know how to fix the prior problem does imply a no. If you don't, then it's important you do; it's your job to support the code you use (not myself, or any other users on SSC). If you need to understand please do ask about it and I'll be happy to explain.

Hi Thom
I have a good look at it and im afraid i dont understand how the code works

Also I would like to output the Select statement on one line as follows:

SELECT [Col1] AS [AccountNumber], [Col56] AS [SHAREBALANCE], [Col57] AS [NewTakedaShares], [Col58] AS [STERLINGDUESCHEMECASH],[Col59] AS [FRACTIONALGBPDUE] FROM ##CAFileData21;

Thom A SSC Guru Points: 99130 More actions · Answer 12

Weegee2017 - Tuesday, January 29, 2019 5:48 AM
Thom A - Monday, January 28, 2019 5:05 AM
Weegee2017 - Monday, January 28, 2019 4:58 AM
Thats great. Thank you very much Thom
Do you understand how it works however? The fact that you didn't know how to fix the prior problem does imply a no. If you don't, then it's important you do; it's your job to support the code you use (not myself, or any other users on SSC). If you need to understand please do ask about it and I'll be happy to explain.
Hi Thom
I have a good look at it and im afraid i dont understand how the code works
Also I would like to output the Select statement on one line as follows:
SELECT [Col1] AS [AccountNumber], [Col56] AS [SHAREBALANCE], [Col57] AS [NewTakedaShares], [Col58] AS [STERLINGDUESCHEMECASH],[Col59] AS [FRACTIONALGBPDUE] FROM ##CAFileData21;

Are there any particular parts you don't understand? Also, including formatting in your dynamic SQL is just as important as in your "normal" (non-dynamic) SQL. Well formatted SQL is far easier to read, and debug; I would suggest leaving the formatting in place; unless you have a good reason why not to? If so, please do share. 😎

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 13

Thom A - Monday, January 28, 2019 4:47 AM
Weegee2017 - Monday, January 28, 2019 4:31 AM
Thom A - Monday, January 28, 2019 4:08 AM
If I'm understanding correctly, the first mistake you are making is actually pivoting your data from #FileImportTypeColumn. That actually makes things a lot harder. If you return it as a row dataset you can easily construct some dynamic SQL:
DECLARE @SQL nvarchar(MAX);
SET @SQL = N'SELECT ' + STUFF((SELECT N',' + NCHAR(10) +
              N'   ' + QUOTENAME(CONCAT(N'Col', ColumnSequence)) + N' AS ' + QUOTENAME(ColumnName)
           FROM #FileImportTypeColumn
           WHERE FileImportTypeID = 30100 --This can be parametrised if needed
            ORDER BY ColumnSequence
           FOR XML PATH(N'')),1,9,N'') + NCHAR(10) +
   N'FROM #OutputTable;'
PRINT @SQL;
EXEC sp_executesql @SQL;
This outputs the following SQL:
SELECT [Col1] AS [AccountNumber],
   [Col56] AS [SHAREBALANCE],
   [Col57] AS [NewTakedaShares],
   [Col58] AS [STERLINGDUESCHEMECASH ],
   [Col59] AS [FRACTIONALGBPDUE],
   [Col60] AS [TOTAL GBP DUE SchemeFraction],
   [Col61] AS [GBPBACSCHANNELSchemeFraction]
FROM #OutputTable;
Hopefully, that is what you are after.
Hi Thom
When I run that , i get the following output
SELECT 1] AS [AccountNumber],
[Col56] AS [SHAREBALANCE],
[Col57] AS [NewTakedaShares],
[Col58] AS [STERLINGDUESCHEMECASH ],
[Col59] AS [FRACTIONALGBPDUE],
[Col60] AS [TOTAL GBP DUE SchemeFraction],
[Col61] AS [GBPBACSCHANNELSchemeFraction]
FROM #OutputTable;
Msg 102, Level 15, State 1, Line 30
Incorrect syntax near ']'
This is why I HATE the pasting on SSC... -_- It got rid of some of the spaces in the whitespace before the first QUOTENAME so it didn't have the correct amount (so STUFF removed more than just the formatting white space)... /sigh. I've attached a txt file.

Copy your code to notepad. Copy from Notepad to between SQL Code tags. The colors are just stupid ugly but the indentation and embedded spaces stick that way.

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

Thom A SSC Guru Points: 99130 More actions · Answer 14

Jeff Moden - Tuesday, January 29, 2019 6:08 AM
Thom A - Monday, January 28, 2019 4:47 AM
Weegee2017 - Monday, January 28, 2019 4:31 AM
Thom A - Monday, January 28, 2019 4:08 AM
If I'm understanding correctly, the first mistake you are making is actually pivoting your data from #FileImportTypeColumn. That actually makes things a lot harder. If you return it as a row dataset you can easily construct some dynamic SQL:
DECLARE @SQL nvarchar(MAX);
SET @SQL = N'SELECT ' + STUFF((SELECT N',' + NCHAR(10) +
              N'   ' + QUOTENAME(CONCAT(N'Col', ColumnSequence)) + N' AS ' + QUOTENAME(ColumnName)
           FROM #FileImportTypeColumn
           WHERE FileImportTypeID = 30100 --This can be parametrised if needed
            ORDER BY ColumnSequence
           FOR XML PATH(N'')),1,9,N'') + NCHAR(10) +
   N'FROM #OutputTable;'
PRINT @SQL;
EXEC sp_executesql @SQL;
This outputs the following SQL:
SELECT [Col1] AS [AccountNumber],
   [Col56] AS [SHAREBALANCE],
   [Col57] AS [NewTakedaShares],
   [Col58] AS [STERLINGDUESCHEMECASH ],
   [Col59] AS [FRACTIONALGBPDUE],
   [Col60] AS [TOTAL GBP DUE SchemeFraction],
   [Col61] AS [GBPBACSCHANNELSchemeFraction]
FROM #OutputTable;
Hopefully, that is what you are after.
Hi Thom
When I run that , i get the following output
SELECT 1] AS [AccountNumber],
[Col56] AS [SHAREBALANCE],
[Col57] AS [NewTakedaShares],
[Col58] AS [STERLINGDUESCHEMECASH ],
[Col59] AS [FRACTIONALGBPDUE],
[Col60] AS [TOTAL GBP DUE SchemeFraction],
[Col61] AS [GBPBACSCHANNELSchemeFraction]
FROM #OutputTable;
Msg 102, Level 15, State 1, Line 30
Incorrect syntax near ']'
This is why I HATE the pasting on SSC... -_- It got rid of some of the spaces in the whitespace before the first QUOTENAME so it didn't have the correct amount (so STUFF removed more than just the formatting white space)... /sigh. I've attached a txt file.
Copy your code to notepad. Copy from Notepad to between SQL Code tags. The colors are just stupid ugly but the indentation and embedded spaces stick that way.

Even that doesn't work for me. It's why I use VSCode as the interim, as it paste a fixed-width font with colouring; making th fixing easier

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk