Combine multiple SQL rows/columns into one single row

Question

I am currently trying to transform a series of rows into one row so that the data can be displayed onto a report.

My data is being presented in my table CustomData like so:

CustomDataID(PK) CustomDataDefinition   ReferenceTablePKValue   IntValue    DateTimeValue   StringValue
1                      Number                   1638              1230          NULL           NULL
2                     1stDate                   1638              NULL       2014-09-23        NULL
3                     2ndDate                   1638              NULL       2014-09-25        NULL
4                     3rdDate                   1638              NULL       2014-09-25        NULL
5                      Notes                    1638              NULL          NULL         Test note.

My goal is to have something like this. Essentially I need it to bring it in as one row.

Number   1stDate       2ndDate     3rdDate       Notes
1230    2014-09-23   2014-09-25   2014-09-25   Test note.

I have tried various SELECT statements with multiple JOINs, but that has not proven fruitful at all. I'm thinking that potentially a temporary table will work, but I'm really not too familiar with how that would work. Does anyone have any ideas on how I could potentially transform this data appropriately? Please let me know if you need further information.

search for "pivot" or "cross tab". This has been answered about a zillion times all over the place. — Sean Lange
– Sean Lange, Commented Sep 23, 2014 at 20:05

neshkeev · Accepted Answer · 2014-09-23 21:03:15Z

Since there are different types you can't use pivot here, so you can use the functionality that was before the pivot function:

with t(CustomDataID, CustomDataDefinition, ReferenceTablePKValue
       , IntValue, DateTimeValue, StringValue) as (
  select 1, 'Number', 1638, 1230, NULL, NULL union all
  select 2, '1stDate', 1638, NULL, '2014-09-23', NULL union all
  select 3, '2ndDate', 1638, NULL, '2014-09-25', NULL union all
  select 4, '3rdDate', 1638, NULL, '2014-09-25', NULL union all
  select 5, 'Notes', 1638, NULL, NULL, 'Test note'
)
select ReferenceTablePKValue
     , max(case 
             when CustomDataDefinition = 'Number' then intvalue 
           end) "number"
     , max(case 
             when CustomDataDefinition = '1stDate' then datetimevalue
           end) "1stDate"
     , max(case 
             when CustomDataDefinition = '2ndDate' then datetimevalue
           end) "2ndDate"
     , max(case 
             when CustomDataDefinition = '3rdDate' then datetimevalue
           end) "3rdDate"
     , max(case 
             when CustomDataDefinition = 'Notes' then stringvalue
           end) "Notes"
  from t
 group by ReferenceTablePKValue

REFERENCETABLEPKVALUE   NUMBER   1STDATE     2NDDATE     3RDDATE    NOTES
-----------------------------------------------------------------------------
                 1638     1230  2014-09-23  2014-09-25  2014-09-25  Test note

SQLFiddle

lookslikeanevo · Accepted Answer · 2014-09-23 20:28:40Z

0

depends on how many rows youre trying to do, easy way to return exactly what you are looking for would be to use a case statement, if you are trying to do this for a bunch of different variables then doing a bunnch of cse statements isnt fun, at that point i would look into pivoting your data.

select
case when CustomDataDefinition = 'Number' then intvalue end 'Number',
case when CustomDataDefinition = '1stDate' then datetimevalue end '1stDate',
case when CustomDataDefinition = '2ndDate' then datetimevalue end '2ndDate',
case when CustomDataDefinition = '3rdDate' then datetimevalue end '3rdDate',
case when CustomDataDefinition = 'Notes' then stringvalue end 'Notes'



from
*yourtable*

answered Sep 23, 2014 at 20:28

lookslikeanevo

5731 gold badge5 silver badges15 bronze badges

Collectives™ on Stack Overflow

Combine multiple SQL rows/columns into one single row

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related