fetch distinct column values in a single row

Question

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

Which DBMS are you using? Oracle version 11 or higher has the PIVOT command which can do exactly what you want. — Colin 't Hart, Oct 29 '12 at 12:44

Taryn · Accepted Answer · 2012-10-29T13:55:35.543

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

You should probably stipulate that your UNPIVOT/PIVOT queries are assuming that `data1` and `data2` are of the same type. — Andriy M, Oct 29 '12 at 13:50
thanks a tonn.. i had just removed aggregate function max() & it worked successfully :):) — puneet2289, Oct 30 '12 at 06:26

score 0 · Answer 2 · answered Oct 29 '12 at 12:39

0

Get the data from the query itself.

Get the id and name from the query1 and concatenate that query with the monthly, quaterly and annually data using the id fetched from the query1

answered Oct 29 '12 at 12:39

Srinivas B

1,821
4
17
35

score 0 · Answer 3 · edited May 23 '17 at 12:22

0

three use full articles for your question:

How to Display rows as Columns in MySQL?

How to pivot a MySQL entity-attribute-value schema

http://forums.mysql.com/read.php?20,75357,75357#msg-75357

edited May 23 '17 at 12:22

Community

1
1

answered Oct 29 '12 at 12:57

Anam

11,999
9
49
63

score 0 · Answer 4 · edited Oct 29 '12 at 17:13

First of all you should retrieve all differents id's into a temporal table..

Select DISTINCT id
into #tmpIds
from Table

Then create a temporal table with the columns you want (id, name, monthlYdata1, monthlYdata2, quaterlydata1, quaterlydata2, halfYearlydata1, halfYearlydata2, annuallydata1, annuallydata2), and loop through your first temporary table in order to get the id's.

For each id you should do:

Insert into #tmpTable
   @id,
   '',
  (Select data1 from Table where id=@id and period='monthly') as monthlyData1
  ...

This is the first solution that came to my mind.

fetch distinct column values in a single row

4 Answers4