SQL Pivot With n Columns into Single Row

Question

I have XML files will engineering test data in them. There are n possible parameter definitions for any given XML file, but the parameter definitions are of a fixed construct.

<ParameterDef>
    <ParameterDefId>1</ParameterDefId>
    <Name>name</Name>
    <Description>desc</Description> 
    <LowLimit>0.00E+00</LowLimit>
    <HighLimit>1.00E+03</HighLimit>
    <SpecLowLimit>-1.00E+32</SpecLowLimit>
    <SpecHighLimit>1.00E+32</SpecHighLimit>
</ParameterDef>

I created a table for the parameter definitions and insert my n parameter definitions into a table.

ParameterDefId	Name	Description	LowLimit	HighLimit	SpecLowLimit	SpecHighLimit
1	Test_One	Test_One_Desc	0.00E+00	1.00E+03	-1.00E+32	1.00E+32
2	Test_Two	Test_Two_Desc	2.00E-15	2.00E-10	-1.00E+32	1.00E+32

For reasons, I need to pull these n parameter definitions into a single row. I understand I can use a PIVOT for this in theory, but I’m getting the selected values returned mostly NULL:

SELECT [1], [2], [3], [4], [5], [6], [7], [8]   
FROM (
    SELECT ParameterDefId, [Name], LowLimit, HighLimit, SpecLowLimit, SpecHighLimit
    FROM dbo.ParameterDefinitions def
        
) dat
PIVOT(
    MAX(LowLimit) -- Max(HighLimit), etc
    FOR ParameterDefId IN ([1], [2], [3], [4], [5], [6], [7], [8])
    
)pvt

If possible, I would like to alias my columns when I pivot so that each column name is unique for the row from which it came: ie, Test_One_LowLimit, Test_One_HighLimit, etc. I can’t seem to find a way to programmatically make the alias.

I can’t be the first person to need something like this, but I’m new to PIVOTs and I’m not entirely sure I know what I’m doing (which is probably obvious).

I would prefer to avoid d-sql, but I’m aware that might not be possible; but I am still not sure how to formulate the query I need in d-sql.

To be clear, this is what I'm after:

ParentTestData	Test_One_LowLimit	Test_One_HighLimit	etc	Test_Two_Lowlimit	Test_Two_HighLimit
something	0.00E+00	1.00E+03	etc	2.00E-15	2.00E-10

I may have the wrong idea of what PIVOT does, but in that case I still need a way to get to this.

Edit 1: It's SQL Server 11 if that's relevant.

Edit 2: It's clear from some comments in my attempt to be concise, I have not communicated clearly what I'm looking for.

I have two tables currently. One has test data in it. One has the parameter definitions of those tests. The parameter definitions for a given test is a variable number of definitions. The parameter definitions table I'm pulling from has a defined structure.

Now, when I pull the data from these two tables to feed to an external piece of software I don't control, I need to provide it all of the data for a given test as one row, regardless how many entries a given test has in the parameter definitions table.

I don't need to keep this. I don't need to store it anywhere; the storage happens before this part of the process is even relevant. I just need to know how to pull the data from the tables I have and put it in the format I need it in.

If there are alternatives to PIVOT or this can be accomplished using a handful of CTEs (or even a cursor as a last resort), I'm fine with that and open to suggestions.

As long as the final product can be easily understood and easy to maintain, I don't particularly care how it's accomplished. I'm asking about a PIVOT because that's my understanding of how to turn rows into columns in SQL server.

What I'm trying to avoid is having to create a table for every product and test the clients have or invent along with all the associated plumbing and maintenance. This will be written into a SPROC, so a series of queries to get the end result is fine.

If there's still confusion about what I'm trying to accomplish, please let me know how to communicate with you more clearly what my end goal is. Thanks.

`PIVOT` only does one column. For multiple columns (and even one column to be honest) you can use conditional aggregation of the form `MAX(CASE WHEN ParameterDefId = 1 THEN LowLimit END)` etc — Charlieface, Feb 14 '22 at 22:26
Is it always just two rows you want to bring side by side? or would you expect Test_Three, etc.., i.e. indefinite number of columns (can we put a cap on n?)? — tinazmu, Feb 14 '22 at 22:30
@tinazmu - No, it's not just two rows. One definition I'm using as my test case has 8 rows, the other has 147. — mattr, Feb 14 '22 at 22:41
@Charlieface - I see. For ParameterDefId = 1 I need all the relevant columns in the row, as I tried to describe above: 1_Low, 1_High, etc, 2_Low, ..., n_Low, n_high I am open to alternatives to pivot if you know of something better to achieve this; thank you. — mattr, Feb 14 '22 at 22:44
So you do multiple aggregation columns: `MAX(CASE WHEN ParameterDefId = 1 THEN LowLimit END), MAX(CASE WHEN ParameterDefId = 1 THEN HighLimit END), MAX(CASE WHEN ParameterDefId = 1 THEN SpecLowLimit END)` and repeat for each possible value of `ParameterDefId`. It's pretty tedious, but that's the easiest way to do this — Charlieface, Feb 14 '22 at 22:48
@Charlieface - thank you for the clarification. That does sound tedious, and would probably be more useful in a d-sql scenario (which I would be okay with if the whole process doesn't wind up being in d-sql). However, when I try to do multiple aggregation columns, my SSMS reports a syntax error at CASE. I updated my question to give the version number as SQL Server 11 if that's relevant. Thank you. — mattr, Feb 14 '22 at 23:06
Trying to rotate a variable number of rows into a variable number of columns is a SQL Anti-Pattern. Why do you 'need' to do that? *(You're trying to force a square peg into a round hole, rethinking your entire mental model to use a normalised structure, much like you already have, will likely avoid more pain in the long run.)* — MatBailie, Feb 14 '22 at 23:15
It's not just "not spectacular" it's the opposite of how the language is designed and how the DB engine is built. If this is intended to end up persisted in a table, doing this is just wrong. If this data is ever going to be consumed by any other SQL, doing this is just wrong. If it's for display purposes, it should be done in your presentation layer, not the database. Right now, my twenty years of experience are screaming at me "don't do this". (But, if you must, dynamic-sql is the only way, BECAUSE the language is DESIGNED to not do this, on purpose.) — MatBailie, Feb 14 '22 at 23:23
@MatBailie: Yes, I'm aware it's not a spectacular idea and you're right about normalized models. But I'm trying to avoid having to define a table and all the requisite subparts for each test these people want to run and collect data for. I have no desire to create a 147-column table and associated plumbing, as well as an 8-column, etc. I'm limited on one end by a commercial product that requires this stuff to be in one row, and I'm trying to avoid having to build new tables and such every time they have a new product or test. There's only one of me I'd be overrun! :) Thank you. — mattr, Feb 14 '22 at 23:26
Why would you need to create a 147 column table? Your question describes a table with 7 columns; 147 rows for one case, and 8 rows for another case, but always the same columns, and so always the same signature. Pivoting this to 1+4n columns (as you say your commercial product requires) would require a different data structure for each case (because of the differing numbers of columns), and persisting those would require multiple differing tables... — MatBailie, Feb 14 '22 at 23:40
@Charlieface - thank you; I misunderstood where you were suggesting your solution (MAX CASE..., but this is what I ended up going with. I appreciate your patience. — mattr, Feb 15 '22 at 21:13
@lptr - thank you; that helped with my custom aliasing. I appreciate it. — mattr, Feb 16 '22 at 15:24

score 1 · Accepted Answer · answered Feb 15 '22 at 00:31

The template you are looking for is the one @charlieface mentioned...

SELECT

  MAX(CASE WHEN ParameterDefId = 1 THEN [Name]        END)   AS Name_1,
  MAX(CASE WHEN ParameterDefId = 1 THEN LowLimit      END)   AS LowLimit_1,
  MAX(CASE WHEN ParameterDefId = 1 THEN HighLimit     END)   AS HighLimit_1,
  MAX(CASE WHEN ParameterDefId = 1 THEN SpecLowLimit  END)   AS SpecLowLimit_1,
  MAX(CASE WHEN ParameterDefId = 1 THEN SpecHighLimit END)   AS SpecHighLimit_1,

  MAX(CASE WHEN ParameterDefId = 2 THEN [Name]        END)   AS Name_2,
  MAX(CASE WHEN ParameterDefId = 2 THEN LowLimit      END)   AS LowLimit_2,
  MAX(CASE WHEN ParameterDefId = 2 THEN HighLimit     END)   AS HighLimit_2,
  MAX(CASE WHEN ParameterDefId = 2 THEN SpecLowLimit  END)   AS SpecLowLimit_2,
  MAX(CASE WHEN ParameterDefId = 2 THEN SpecHighLimit END)   AS SpecHighLimit_2

FROM
  yourTable

You'll need Dynamic SQL to implement this (be that written in T-SQL or externally to the database).

Query the data to establish the number of parameter ids (or a list of them, if they're not sequential), then repeat the MAX(CASE) expressions n times.

This wasn't the solution to the entire problem, but this was enough to get me where I needed to be. Thanks to both you and @charlieface — mattr, Feb 15 '22 at 21:12

SQL Pivot With n Columns into Single Row

1 Answers1