How to Transform Vertical Data into Horizontal Data with SQL

How to transform vertical data into horizontal data with SQL?

Regardless of the database you are using, the concept of what you are trying to achieve is called "Pivot Table".

Here's an example for mysql:
http://en.wikibooks.org/wiki/MySQL/Pivot_table

Some databases have builtin features for that, see the links below.

SQLServer:
http://msdn.microsoft.com/de-de/library/ms177410.aspx

Oracle:
http://www.dba-oracle.com/t_pivot_examples.htm

You can always create a pivot by hand. Just select all the aggregations in a result set and then select from that result set. Note, in your case, you can put all the names into one column using concat (i think that's group_concat in mysql), since you cannot know how many names are related to a a rel_id.

pseudo-select for your case (i don't know mysql):

select rel_id, group_concat(name) from item group by rel_id

Dynamically convert vertical to horizontal data in SQL Server

If number of site values are fixed then you can use the conditional aggregation as follows:

select item,
       max(case when site = 1 then 'YES' else 'NO' end) as site_1,
       max(case when site = 2 then 'YES' else 'NO' end) as site_2,
       max(case when site = 3 then 'YES' else 'NO' end) as site_3,
       max(case when site = 4 then 'YES' else 'NO' end) as site_4,
       max(case when site = 5 then 'YES' else 'NO' end) as site_5,
       max(case when site = 6 then 'YES' else 'NO' end) as site_6,
       max(case when site = 7 then 'YES' else 'NO' end) as site_7
  from your_table
group by item;

How to transpose the vertical data into horizontal by using BigQuery?

Below is for BigQuery Standard SQL

#standardSQL
SELECT 
  EmployeeName,
  IF(Relationship IS NULL, 'Self', Relationship) Relationship, 
  IFNULL(Name, EmployeeName) Name, 
  MAX(IF(BenefitCode = 'DEN', IFNULL(BenefitOption, BenefitCode), NULL)) AS DEN,
  MAX(IF(BenefitCode = 'MED', IFNULL(BenefitOption, BenefitCode), NULL)) AS MEDICAL,
  MAX(IF(BenefitCode = 'VIS', IFNULL(BenefitOption, BenefitCode), NULL)) AS VISION  
FROM `project.dataset.table`
GROUP BY Name, EmployeeName, Relationship 
-- ORDER BY Name, Relationship

If to apply to sample data from your question - result is

Row EmployeeName    Relationship    Name    DEN     MEDICAL VISION   
1   Alice           Self            Alice   EEC     null    null     
2   Alice           CHL             John    EEC     null    null     
3   Alice           SPS             Lee     null    MED     null     
4   Lily            Self            Lily    null    null    VIS  
5   Lily            SPS             Tom     null    null    VIS

Another option would be extend above flatten version into "hierarchical"

#standardSQL
SELECT EmployeeName,
  ARRAY_AGG(STRUCT(Name, Relationship, DEN, MEDICAL, VISION)) benefits
FROM (
  SELECT 
    EmployeeName,
    IF(Relationship IS NULL, 'Self', Relationship) Relationship, 
    IFNULL(Name, EmployeeName) Name, 
    MAX(IF(BenefitCode = 'DEN', IFNULL(BenefitOption, BenefitCode), NULL)) AS DEN,
    MAX(IF(BenefitCode = 'MED', IFNULL(BenefitOption, BenefitCode), NULL)) AS MEDICAL,
    MAX(IF(BenefitCode = 'VIS', IFNULL(BenefitOption, BenefitCode), NULL)) AS VISION  
  FROM `project.dataset.table`
  GROUP BY Name, EmployeeName, Relationship 
) 
GROUP BY EmployeeName
-- ORDER BY EmployeeName

In this case, result will be

Row EmployeeName    benefits.Name   benefits.Relationship   benefits.DEN    benefits.MEDICAL    benefits.VISION  
1   Alice           Alice           Self                    EEC             null                null     
                    John            CHL                     EEC             null                null     
                    Lee             SPS                     null            MED                 null       
2   Lily            Lily            Self                    null            null                VIS  
                    Tom             SPS                     null            null                VIS

SQL Server 2008 Vertical data to Horizontal

You can use the PIVOT function to convert your rows of data into columns.

Your original query can be used to retrieve all the data, the only change I would make to it would be to exclude the column b.field_id because this will alter the final display of the result.

If you have a known list of field_name values that you want to turn into columns, then you can hard-code your query:

select app_id,
  [First Name], [Last Name], [DOB],
  [Mailing Addr], [Zip]
from
(
  SELECT 
     a.app_id, 
     c.field_name,
     b.field_value 
  FROM table1 a
  INNER JOIN table2 b 
    ON a.app_id = b.app_id
  INNER JOIN table3 c 
    ON b.field_id = c.field_id 
) d
pivot
(
  max(field_value)
  for field_name in ([First Name], [Last Name], [DOB],
                     [Mailing Addr], [Zip])
) piv;

See SQL Fiddle with Demo.

But if you are going to have an unknown number of values for field_name, then you will need to implement dynamic SQL to get the result:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(Field_name) 
                    from Table3
                    group by field_name, Field_id
                    order by Field_id
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT app_id,' + @cols + ' 
            from 
            (
              SELECT 
                 a.app_id, 
                 c.field_name,
                 b.field_value 
              FROM table1 a
              INNER JOIN table2 b 
                ON a.app_id = b.app_id
              INNER JOIN table3 c 
                ON b.field_id = c.field_id 
            ) x
            pivot 
            (
                max(field_value)
                for field_name in (' + @cols + ')
            ) p '

execute sp_executesql @query;

See SQL Fiddle with Demo. Both of these this will give a result:

| APP_ID | FIRST NAME | LAST NAME |      DOB |    MAILING ADDR |   ZIP |
------------------------------------------------------------------------
|   1234 |        Joe |     Smith | 10/15/72 |     PO Box 1234 | 12345 |
|   1239 |        Bob |   Johnson | 12/01/78 | 1234 N Star Ave | 12456 |

how to make vertical data to horizontal data in SQL (Dynamic column)

Try this

declare @TotalRows int

DECLARE @Rows Table
(
 RowIndex Int PRIMARY KEY IDENTITY(1,1),
 Parameter_name sysname,
 Data_Type nvarchar(128), 
 CHARACTER_MAXIMUM_LENGTH int 
)

insert into @Rows(Parameter_name,Data_Type, CHARACTER_MAXIMUM_LENGTH)
 SELECT   Parameter_name,Data_Type, CHARACTER_MAXIMUM_LENGTH
 FROM INFORMATION_SCHEMA.PARAMETERS
 WHERE SPECIFIC_NAME = 'SP_Name'
Select @TotalRows=@@ROWCOUNT

  Declare @MaxLen1 int,@MaxLen2 int
  select  @MaxLen1=Max(Len(Parameter_name)) , @MaxLen2=Max(Len(Data_Type))  from @Rows
  Declare @MaxColSize int
  Select @MaxColSize= Case When @MaxLen1>@MaxLen2 Then @MaxLen1 Else @MaxLen2 End

declare @SQL nvarchar(MAX)
set @SQL='Declare @Table Table(
RowIndex int'

declare @I int
set @I=0
While @I < @TotalRows
Begin   
  set @SQL=@SQL+', 
Column'+Cast(@I+1 as nvarchar(5)) + ' nvarchar('+Cast(@MaxColSize as nvarchar(3))+')'
  Set @I=@I+1
End
 set @SQL=@SQL+'
 )
 insert into @Table (RowIndex) values(1),(2),(3) '

Declare 
 @Parameter_name sysname,
 @Data_Type nvarchar(50), 
 @CHARACTER_MAXIMUM_LENGTH int
Declare @RowIndex int
set @RowIndex=1
While @RowIndex < @TotalRows+1
Begin   

   select @Parameter_name =Parameter_name ,@Data_Type =Data_Type ,@CHARACTER_MAXIMUM_LENGTH =CHARACTER_MAXIMUM_LENGTH from @Rows Where RowIndex=@RowIndex
   set @SQL=@SQL+'
Update @Table Set Column'+Cast(@RowIndex as nvarchar(5))+'='''+Replace(@Parameter_name,'''','''''')+''' Where RowIndex=1
Update @Table Set Column'+Cast(@RowIndex as nvarchar(5))+'='''+Replace(ISNULL(@Data_Type,''),'''','''''')+''' Where RowIndex=2
Update @Table Set Column'+Cast(@RowIndex as nvarchar(5))+'='+isNull(Cast(@CHARACTER_MAXIMUM_LENGTH as nvarchar(20)),'NULL')+' Where RowIndex=3'
 set @RowIndex=@RowIndex+1
End
set @SQL=@SQL+'
 Select * from @Table'

 exec sp_executesql @SQL

how to transform vertical fields in a table to horizontal result by SQL