Trying to Flatten Rows into Columns

trying to flatten rows into columns

This data transformation is known as a PIVOT. In SQL Server 2005+ there is a function that will perform this process. :

select *
from
(
  SELECT * 
  FROM mytable
) src
pivot
(
  max(value)
  for field in (first, last)
) piv

See SQL Fiddle with Demo.

Or you can use an aggregate function with a CASE expression:

select rowid,
  max(case when field = 'first' then value end) first,
  max(case when field = 'last' then value end) last
from MyTable
group by rowid

See SQL Fiddle with Demo.

You can also use multiple joins on your table:

select t1.rowid,
  t1.value first,
  t2.value last
from mytable t1
left join mytable t2
  on t1.rowid = t2.rowid
  and t2.field = 'last'
where t1.field = 'first'

See SQL Fiddle with Demo

The result for all versions is the same:

| ROWID | FIRST |  LAST |
-------------------------
|     1 |  neil | young |
|     2 |   bob | dylan |
|     3 |   tom | petty |

T-SQL How to Flatten top 3 rows into a single row

With the window function Row_Number() and a conditional aggregation

 Select CustID
       ,StyleNo1 = max(case when RN=1 then StyleNo else null end)
       ,StyleNo2 = max(case when RN=2 then StyleNo else null end)
       ,StyleNo3 = max(case when RN=3 then StyleNo else null end)
       ,Brand1   = max(case when RN=1 then Brand   else null end)
       ,Brand2   = max(case when RN=2 then Brand   else null end)
       ,Brand3   = max(case when RN=3 then Brand   else null end)
 From  (
         Select *,RN = Row_Number() over (Partition By CustID Order by StyleNo,Brand)
         From  YourTable
       ) A
 Where RN<=3
 Group By CustID

Returns

SQL pivot to flatten rows into columns

Similar to SQLZim's answer. Only difference is that the Window function Row_Number() is used just in case vehicleindex is not a consistent 1 and 2.

Select year1  = max(case when RN=1 then [year] end)
      ,make1  = max(case when RN=1 then make end)
      ,model1 = max(case when RN=1 then model end)
      ,year2  = max(case when RN=2 then [year] end)
      ,make2  = max(case when RN=2 then make end)
      ,model2 = max(case when RN=2 then model end)
 From (
        Select *
              ,RN = Row_Number() over (Partition By parentid Order By vehicleindex)
         From  YourTable
      ) A
 Group By parentid 

EDIT: Option 2 - Use PIVOT

Select *
From (
        Select parentid
              ,item     = concat(B.item,Dense_Rank() over (Partition By parentid Order By vehicleindex))
              ,value
         From  YourTable
         Cross Apply ( values ('year' ,cast(Year as varchar(100)))
                             ,('make' ,make)
                             ,('model',model)
                      ) B (item,value)
     ) A
 Pivot (max(value) For [item] in ([year1],[make1],[model1],[year2],[make2],[model2]) ) p

SQL convert rows to columns and flatten

Is this what you want?

select t1.partnum, t2.width, t2.height, t2.depth, count(*) as cnt
from t1 join
     (select t2.orderid,
             sum(case when ccode = 'width' then value end) as width,
             sum(case when ccode = 'height' then value end) as height,
             sum(case when ccode = 'depth' then value end) as depth
      from t2
      group by t2.orderid
     ) t2
     on t2.orderid = t1.orderid
group by t1.partnum, t2.width, t2.height, t2.depth;

I might speculate that you want:

sum(t2.width * t2.height * t2.depth) as area

but the numbers disagree with the values in your question.

Here is a db<>fiddle.

How to flatten rows into columns and specify column names

Another Option is to use some XML to convert the data to a EAV Structure (Entity Attribute Value)

This approach is very dynamic, and will generate the appropriate number of "groups" in the proper order.

Declare @AttendeeChild table (Id int,AttendeeId int,Age Int,Name varchar(50),Gender varchar(25))
Insert Into @AttendeeChild values
(1,1,10,'Sam','Boy'),
(2,1,9,'Sally','Girl'),
(2,2,9,'Sue','Boy')

-- Convert Data to EAV Structure'ish
Declare @XML xml = (Select *,GrpSeq=Row_Number() over (Partition By AttendeeId Order By Age Desc) from @AttendeeChild for XML RAW)
Select AttendeeId = r.value('@AttendeeId','int')
      ,GrpSeq     = r.value('@GrpSeq','int')
      ,ColSeq     = Row_Number() over (Partition By r.value('@AttendeeId','int') Order By (Select NULL))
      --,Item     = attr.value('local-name(.)','varchar(100)')
      ,Element    = 'Child'+r.value('@GrpSeq','varchar(10)')+attr.value('local-name(.)','varchar(100)')
      ,Value      = attr.value('.','varchar(max)') 
 Into  #Temp
 From  @XML.nodes('/row') as A(r)
 Cross Apply A.r.nodes('./@*') AS B(attr)
 Where attr.value('local-name(.)','varchar(100)') not in ('ID','AttendeeId','GrpSeq')

-- Get Cols in correct Order
Declare @Cols varchar(max) = Stuff((Select ',' + QuoteName(Element) 
                                     From  (Select Distinct Top 100 Percent  ColSeq,Element From #Temp Order By ColSeq ) A
                                     For XML Path(''), Type
                                    ).value('.', 'varchar(max)'),1,1,'')

-- Execute Dynamic Pivot
Declare @SQL varchar(max) = '
Select *
 From (Select AttendeeId,Element,Value From #Temp) T
 Pivot (
        max(Value)
        For [Element] in (' + @Cols + ')
       ) P '

Exec(@SQL)

Returns

How to flatten a table from row to columns

You cold use a couple of inline queries

select 
    IssueKey,
    (
        select t1.NewValue 
        from mytable t1 
        where t1.IssueKey = t.IssueKey and t1.Field = 'Risk Severity'
        order by ChangeDate desc limit 1
    ) `Risk Severity`,
    (
        select t1.NewValue 
        from mytable t1 
        where t1.IssueKey = t.IssueKey and t1.Field = 'Risk Occurrence'
        order by ChangeDate desc limit 1
    ) `Risk Occurrence`,
    (
        select t1.NewValue 
        from mytable t1 
        where t1.IssueKey = t.IssueKey and t1.Field = 'Risk Detection'
        order by ChangeDate desc limit 1
    ) `Risk Severity`,
    max(ChangeDate) ChangeDate
from mytable t
group by IssueKey

With an index on (IssueKey, Field, ChangeDate, NewValue), this should an efficient option.

Demo on DB Fiddle:

IssueKey | Risk Severity | Risk Occurrence | Risk Severity | ChangeDate     
:------- | ------------: | --------------: | ------------: | :------------------
HRSK-184 |             3 |               2 |             5 | 2019-10-26 09:11:01

Flatten table rows into columns in SQL Server

I would use conditional aggregate along with row_number():

select code,
       max(case when seqnum = 1 then code end) as code_1,
       max(case when seqnum = 2 then code end) as code_2,
       max(case when seqnum = 3 then code end) as code_3
from (select t.*,
             row_number() over (partition by code order by data) as seqnum
      from t
     ) t
group by code;

flatten data in SQL based on fixed set of column

You can use arrays:

select id,
       array_agg(name order by name)[safe_ordinal(1)] as name_1,
       array_agg(property order by name)[safe_ordinal(1)] as property_1,
       array_agg(name order by name)[safe_ordinal(2)] as name_2,
       array_agg(property order by name)[safe_ordinal(2)] as property_2,
       array_agg(name order by name)[safe_ordinal(3)] as name_3,
       array_agg(property order by name)[safe_ordinal(3)] as property_3
from t
group by id;

Trying to flatten data into columns within oracle

A little bit of aggregation along with decode (or case, whichever you prefer; in this case, decode is quite simple) might do the job.

SQL> with edit_raw_table (table_id, edit_number, edit_ref, edit_text, edit_valid) as
  2    (select 1, 3, 10146, 'Reason 123', 'yes' from dual union all
  3     select 2, 3, 10169, 'Reason 567', 'yes' from dual union all
  4     select 3, 3, 10156, 'Reason 456', 'no'  from dual union all
  5     --
  6     select 4, 8, 10111, 'reason 111', 'yes' from dual union all
  7     --
  8     select 5, 4, 20222, 'reason 222', 'no'  from dual union all
  9     select 6, 4, 20333, 'reason 333', 'yes' from dual union all
 10     select 7, 4, 20444, 'reason 444', 'yes' from dual union all
 11     select 8, 4, 20555, 'reason 555', 'yes' from dual
 12    ),
 13  temp as
 14    (select e.*,
 15            row_number() over (partition by edit_number order by table_id) rn
 16     from edit_raw_table e
 17     where edit_valid = 'yes'
 18    )
 19  select edit_number,
 20         max(decode(rn, 1, edit_ref)) edit_ref_1,
 21         max(decode(rn, 1, edit_text)) edit_text_1,
 22         --
 23         max(decode(rn, 2, edit_ref)) edit_ref_2,
 24         max(decode(rn, 2, edit_Text)) edit_text_2,
 25         --
 26         max(decode(rn, 3, edit_ref)) edit_ref_3,
 27         max(decode(rn, 3, edit_Text)) edit_text_3,
 28         --
 29         max(decode(rn, 4, edit_ref)) edit_ref_4,
 30         max(decode(rn, 4, edit_Text)) edit_text_4
 31  from temp
 32  group by edit_number
 33  order by edit_number;

EDIT_NUMBER EDIT_REF_1 EDIT_TEXT_ EDIT_REF_2 EDIT_TEXT_ EDIT_REF_3 EDIT_TEXT_ EDIT_REF_4 EDIT_TEXT_
----------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
          3      10146 Reason 123      10169 Reason 567
          4      20333 reason 333      20444 reason 444      20555 reason 555
          8      10111 reason 111

SQL>

---

Related Topics