question

TomWellborn-9533 avatar image
0 Votes"
TomWellborn-9533 asked pituach edited

Azure Sql ETL with pivot

ETL SQL

Simply stated question:

I'm writing an ETL query between two databases in Azure SQL.

Source:

One table with two columns: The first column contains the text of one a predetermined set of questions, and the second column contains the text of the response.

Target: Pivot the above into tables with columns named for all possible questions, to be populated with the response data.

I've been trying different types of pivots and the only way I've been able to get this work is by typing out every single column. I need to populate in the target. I'm looking for a more elegant idea to pursue.

Thanks, and always remember to carry a towel

azure-sql-database
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @TomWellborn-9533, welcome to Microsoft Q&A forum.

Would it be possible to provide the source table with couple of rows in tabular format and the target what we want to achieve in tabular format, that way we could try to work on exact query?

0 Votes 0 ·

1 Answer

pituach avatar image
0 Votes"
pituach answered pituach edited

Good day,

I'm looking for a more elegant idea to pursue.

I think that the source of the issue here is with your definition of "elegant" and the desire to get shorter text for the query instead of getting better query.

The elegant way is to get the best performance and NOT to get shorter query text!

In most case using the list of columns explicitly is the right way to go and is the elegant way to write the code for better query.

Note: If you can provide the information to reproduce your scenario then we can have a discussion about specific solutions (queries). For this we need to get from you queries to create the relevant table(s) and to insert some sample data + the requested result which you want to get using this sample data (the sample data should cover all possible options)

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.