puneet2289 puneet2289 - 5 months ago 12
SQL Question

fetch distinct column values in a single row

This is the data:

id name period data1 data2
===================================================
111 name1 monthly aaaaa bbbbb
111 name1 quaterly ccccc ddddd
111 name1 halfYearly eeeee fffff
111 name1 annually ggggg hhhhh


I want query which fetch data in a single row like

id name monthlYdata1 monthlYdata2 quaterlydata1 quaterlydata2 halfYearlydata1 halfYearlydata2 annuallydata1 annuallydata2
==========================================================================================================================================================
111 name1 aaaaa bbbbb ccccc ddddd eeeee fffff ggggg hhhhh

Answer

You did not specify what RDBMS you are using but, this will work in all of them:

select id,
  name,
  max(case when period = 'monthly' then data1 end) as MonthlyData1,
  max(case when period = 'monthly' then data2 end) as MonthlyData2,
  max(case when period = 'quaterly' then data1 end) as quarterlyData1,
  max(case when period = 'quaterly' then data2 end) as quarterlyData2,
  max(case when period = 'halfYearly' then data1 end) as halfYearlyData1,
  max(case when period = 'halfYearly' then data2 end) as halfYearlyData2,
  max(case when period = 'annually' then data1 end) as annuallyData1,
  max(case when period = 'annually' then data2 end) as annuallyData2
from yourtable
group by id, name

See SQL Fiddle with Demo

If you are using an RDBMS that has a PIVOT function, then you can do the following which uses both an UNPIVOT and PIVOT to produce the results. As Andriy M pointed out the UNPIVOT is assuming that the datatype for both data1 and data2 are the same types, if not, then a conversion would need to take place to UNPIVOT the data:

Oracle 11g:

select *
from
(
  select id, name, value, 
    period||data new_col
  from yourtable
  unpivot
  (
    value for data in (data1, data2)
  ) u
) x
pivot
(
  max(value)
  for new_col in ('monthlyDATA1', 'monthlyDATA2',
                  'quaterlyDATA1', 'quaterlyDATA2',
                  'halfYearlyDATA1', 'halfYearlyDATA2',
                  'annuallyDATA1', 'annuallyDATA2')
) p

See SQL Fiddle with Demo

SQL Server:

select *
from
(
  select id, name, value, 
    period+data new_col
  from yourtable
  unpivot
  (
    value for data in (data1, data2)
  ) u
) x
pivot
(
  max(value)
  for new_col in ('monthlyDATA1', 'monthlyDATA2',
                  'quaterlyDATA1', 'quaterlyDATA2',
                  'halfYearlyDATA1', 'halfYearlyDATA2',
                  'annuallyDATA1', 'annuallyDATA2')
) p