T-SQL Query change multiple Columns to Single Column Grouped by Date

Question

I am attempting to change the table structure of our RAW data from wide table to narrow table.

So I have managed to make it work for one group of columns. The issue starts when i try to make more columns.

I've tried using just an UNPIVOT but that doesn't seem to work.

So I have 10 columns(each representing a year for that value) for each group of data and I want to turn it into one column.

E.g. Going from [YREnd1], [YREnd2], [YREnd3] etc. to [YREnd]. I have managed it for one group however, when i try to add e.g. [Tangible] it just repeats the first years values instead of using the next value.

I suspect i am just missing a join to match the year end of the tangible to year end in first half but i dont know how i would go about doing that.

This works:

SELECT [PandL1].[RegNum],
       c.[YrEnd]
  FROM [Raw_UK].[dbo].[PandL1]
  JOIN [Raw_UK].[dbo].[PandL2] on [PandL1].[RegNum]=[PandL2].[RegNum]
  JOIN [Raw_UK].[dbo].Balance1 on Balance1.[RegNum] = [PandL1].[RegNum]
  JOIN [Raw_UK].[dbo].Balance2 on Balance2.[RegNum] = [PandL1].[RegNum]
  JOIN [Raw_UK].[dbo].Balance3 on Balance3.[RegNum] = [PandL1].[RegNum]
Cross apply
(
    Values
    ('[Accounts date Last avail  yr]', [Accounts date Last avail  yr]),
    ('[Accounts date Year - 1]', [Accounts date Year - 1]),
    ('[Accounts date Year - 2]', [Accounts date Year - 2]),
    ('[Accounts date Year - 3]', [Accounts date Year - 3]),
    ('[Accounts date Year - 4]', [Accounts date Year - 4]),
    ('[Accounts date Year - 5]', [Accounts date Year - 5]),
    ('[Accounts date Year - 6]', [Accounts date Year - 6]),
    ('[Accounts date Year - 7]', [Accounts date Year - 7]),
    ('[Accounts date Year - 8]', [Accounts date Year - 8]),
        ('[Accounts date Year - 9]', [Accounts date Year - 9])
    ) c ([RegNum],[YrEnd])
    GROUP BY [PandL1].[RegNum],
           c.[YrEnd];

This doesn't work:

    SELECT [PandL1].[RegNum],
           c.[YrEnd],
           t.[Tangibles]
      FROM [Raw_UK].[dbo].[PandL1]
      JOIN [Raw_UK].[dbo].[PandL2] on [PandL1].[RegNum] = [PandL2]               
.[RegNum]
JOIN [Raw_UK].[dbo].Balance1 on Balance1.[RegNum] = [PandL1].[RegNum]
  JOIN [Raw_UK].[dbo].Balance2 on Balance2.[RegNum] = [PandL1].[RegNum]
  JOIN [Raw_UK].[dbo].Balance3 on Balance3.[RegNum] = [PandL1].[RegNum]
Cross apply
(
    Values
    ('[Accounts date Last avail  yr]', [Accounts date Last avail  yr]),
    ('[Accounts date Year - 1]', [Accounts date Year - 1]),
    ('[Accounts date Year - 2]', [Accounts date Year - 2]),
    ('[Accounts date Year - 3]', [Accounts date Year - 3]),
    ('[Accounts date Year - 4]', [Accounts date Year - 4]),
    ('[Accounts date Year - 5]', [Accounts date Year - 5]),
    ('[Accounts date Year - 6]', [Accounts date Year - 6]),
    ('[Accounts date Year - 7]', [Accounts date Year - 7]),
    ('[Accounts date Year - 8]', [Accounts date Year - 8]),
    ('[Accounts date Year - 9]', [Accounts date Year - 9])
) c ([RegNum],[YrEnd])
Cross apply
(
    Values
    ('[Tangible Assets th GBP Last avail  yr]', [Tangible Assets th GBP    
Last avail  yr]),
    ('[Tangible Assets th GBP Year - 1]',[Tangible Assets th GBP Year - 1]),
    ('[Tangible Assets th GBP Year - 2]',[Tangible Assets th GBP Year - 2]),
    ('[Tangible Assets th GBP Year - 3]',[Tangible Assets th GBP Year - 3]),
    ('[Tangible Assets th GBP Year - 4]',[Tangible Assets th GBP Year - 4]),
    ('[Tangible Assets th GBP Year - 5]',[Tangible Assets th GBP Year - 5]),
    ('[Tangible Assets th GBP Year - 6]',[Tangible Assets th GBP Year - 6]),
    ('[Tangible Assets th GBP Year - 7]',[Tangible Assets th GBP Year - 7]),
    ('[Tangible Assets th GBP Year - 8]',[Tangible Assets th GBP Year - 8]),
    ('[Tangible Assets th GBP Year - 9]',[Tangible Assets th GBP Year - 9])
)
t ([RegNum], [Tangibles])
GROUP BY [PandL1].[RegNum],
       c.[YrEnd],
       t.[Tangibles];

The result should be:

GB********, 31/12/2018, 3575
GB********, 31/12/2017, 3187
GB********, 31/12/2016, 3084
GB********, 31/12/2015, 2813
GB********, 31/12/2014, 2758
GB********, 31/12/2013, 2621
GB********, 31/12/2012, 2304
GB********, 31/12/2011, 2298
GB********, 31/12/2010, 2045
GB********, 31/12/2009, 1967

What I am getting is:

GB********, 31/12/2018, 3575
GB********, 31/12/2017, 3575
GB********, 31/12/2016, 3575
GB********, 31/12/2015, 3575
GB********, 31/12/2014, 3575
GB********, 31/12/2013, 3575
GB********, 31/12/2012, 3575
GB********, 31/12/2011, 3575
GB********, 31/12/2010, 3575
GB********, 31/12/2009, 3575

score 0 · Answer 1 · answered Aug 27 '19 at 10:30

Hi there so after some more research in to this i have an answer thanks to this link: Using PIVOT to Flip Data from Wide to Tall

What i needed to do was this:

Cross apply 
(VALUES
([Accounts date Last avail  yr], [Tangible Assets th GBP Last avail  yr]),
([Accounts date Year - 1], [Tangible Assets th GBP Year - 1]),
([Accounts date Year - 2], [Tangible Assets th GBP Year - 2]),
([Accounts date Year - 3], [Tangible Assets th GBP Year - 3]),
([Accounts date Year - 4], [Tangible Assets th GBP Year - 4]),
([Accounts date Year - 5], [Tangible Assets th GBP Year - 5]),
([Accounts date Year - 6], [Tangible Assets th GBP Year - 6]),
([Accounts date Year - 7], [Tangible Assets th GBP Year - 7]),
([Accounts date Year - 8], [Tangible Assets th GBP Year - 8]),
([Accounts date Year - 9], [Tangible Assets th GBP Year - 9])
) x ([YrEnd], [Tangibles])

T-SQL Query change multiple Columns to Single Column Grouped by Date

1 Answers1