Dynamically Build A Join Of Unknown Fields

Transact-SQL
1

I have a need to convert data in a single column into a table. The issue is that the number of columns in my table will be dynamic.

So I have data which is for example as below:

formid column row Value
001 1 1 Blue
001 2 1 Audi
002 1 1 Red
002 2 1 Audi
001 1 2 Black
001 2 2 Merc

I then need to join on the formid and row so my output would be:

001 | Blue | Audi
001 | Black | Merc
002 | Red | Audi

This is relatively easy to do if i always know there are a set number of columns. As I cycle through different tables however the number of columns changes.

I have tried a pivot query but this always needs some form of aggregation or max/min so only ever returns one row per formid.

I'm not actually sure this can be done so any help is appreciated.

Thank you.

2 
DECLARE @t table
   (  id int identity
    , formid   char(3)
    , [column] int
    , row      int
    , Value    varchar(10)
   ) ;

INSERT
   @t (
      formid
    , [column]
    , [row]
    , [Value]
      )
VALUES
(
   '001'
 , 1
, 1
 , 'Blue'
)
, (
     '001'
   , 2
   , 1
   , 'Audi'
  )
, (
     '002'
   , 1
   , 1
   , 'Red'
  )
, (
     '002'
   , 2
   , 1
   , 'Audi'
  )
, (
     '001'
   , 1
   , 2
   , 'Black'
  )
, (
     '001'
   , 2
   , 2
   , 'Merc'
  ) ;
SELECT
   t.formid
 , t.[column]
 , t.row
 , t.Value
FROM
   @t t ;
declare @NumCols int;
SELECT
	@NumCols=Max(t.[column])-1
FROM
	@t t;
SELECT
   t1.formid
 , t1.Value Color
 , t2.Value Make
FROM
   @t t1
JOIN
   @t t2 ON t1.formid = t2.formid
WHERE
   t1.[column]  = 1
AND t2.[column] = 2
AND t1.id + @NumCols = t2.id
ORDER BY
   t1.row
 , t2.row ;

image

3

Thanks for this jotorre but i'm not sure it answers my query unfortunately.

It still seems to need me to declare how many columns I have, the point is that I do not know this, there were two in my example but there could be any number.

The result is also incorrect as row 4 is a Black Audi which doesn't exist in my example data.

Ed

4

Could this be what you're looking for

select formid
      ,max(case when [column]=1 then [value] else null end) as colour
      ,max(case when [column]=2 then [value] else null end) as brand
  from yourtable
 group by formid
         ,[row]
 order by formid
;

Ps.: I haven't tested this, as you didn't provide consumable data, so there might be a "syntax feature" to correct.

6

I changed the query above.

7

Is there a maximum number of possible values? How are you determining which column data to display - is it all available or will there be some input parameter?

Pick a maximum number of columns to be returned - code for that max number. If there isn't data available - those values will be NULL.

8

Thank you bitsmed, again this looks as if I need to know the number of columns, the nature of my dataset means that i won't always know.

I think the solution will be jotorre's second solution which is to over engineer and live with some nulls.

Thank you for your effort though.

Ed

9

jeff,

Thanks, this was the way I was leaning, I think I'll have to plan for the largest possible case and live with the null values.

Thanks,

Ed