Select column values ​​in one 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 need a query that retrieves data in one row, like

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

Answer 1

You did not specify which 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 use an RDBMS with the PIVOT function, you can do the following, which uses both UNPIVOT and PIVOT to get the results. As Andriy M noted, UNPIVOT assumes that the data type for data1 and data2 are the same types, if not, then the conversion should take place for UNPIVOT 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 

Answer 2

Get data from the request itself.

Get the identifier and name from the query1 and combine this query with monthly, quarterly and annual data using the identifier obtained from the query1

Answer 3

three use full articles for your question:

How to display rows as columns in MySQL?

How to create a MySQL entity attribute schema

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

Answer 4

First of all, you should get all the differents identifiers in a temporary table.

 Select DISTINCT id into #tmpIds from Table 

Then create a temporary table with the desired columns ( id , name , monthlYdata1 , monthlYdata2 , quaterlydata1 , quaterlydata2 , halfYearlydata1 , halfYearlydata2 , annuallydata1 , annuallydata2 ) and scroll through the first temporary table to get the identifiers.

For each identifier you must:

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

This is the first decision that occurred to me.