Advanced SQL Unpivot
Question
Application Type
Reactive

Hi,

I have Table like that:

Person | Cash | Wire

John | 100 | 50

Jack | 50 | 100

What I need is to change it to

Person | Amount | Type

John | 100 | Cash

John | 50 | Wire

Jack | 50 | Cash

Jack | 100 | Wire


I wanted to use UNPIVOT but the trick is - I have to make a few JOINS with other tables and I keep getting errors 

that multi-part identifiers could not be bound.
I think JOINS might be the reason behind this but I'm not sure how to approach this.


SQL looks like this:

SELECT 
    {TableA}.[FirstID],
    CONCAT('ABC', DATEPART(mm, {TableB}.[Date]),'_',DATEPART(yyyy, {TableB}.[Date])) AS DATE_ID,
    CAST( GETDATE() AS Date ) AS Example_DATE, 
    {TableC}.[ExampleVaue] AS EXAMPLE_VALUE,
    {TableA}.[ExampleValue] AS EXAMPLE_VALUE2,
    REPLACE(CAST(AMOUNT AS VARCHAR(15)),',','.'),
    CASE EXAMPLE_TYPE
        WHEN 'EXAMPLE_AMOUNT' THEN 'TYPE1'
        WHEN 'EXAMPLE_AMOUNT2' THEN 'TYPE2'
        ELSE EXAMPLE_TYPE END AS EXAMPLE_TYPE
FROM {TableA}
LEFT JOIN
{TableB}
ON
{TableA}.[TABLEB_ID] = {TableB}.[Id]
LEFT JOIN 
{TableD}
ON 
{TableD}.[EXAMPLE_COLUMN] = {TableA}.[EXAMPLE_COLUMN]
LEFT JOIN
{TableC}
ON 
{TableD}.[C_ID]={TableC}.[Id]
UNPIVOT
(
    AMOUNT
    FOR EXAMPLE_TYPE IN ({TableA}.[EXAMPLE_AMOUNT], {TableA}.[EXAMPLE_AMOUNT2])
)AS DUMMYUNPIVOT


 

Hi WK,

can you share your oml, or a complete overview of your datamodel.  It's hard to try to help.


so if it would only be the table you describe, i would expect something like

select u.[Person], u.[Type], u.[Amount]
from {TableA}
unpivot
(
  Amount
  for Type in (Cash, Wire)
) u;

for example, you are referring to TableA in your select, I think you can only select from u (or in your case DUMMYUNPIVOT)


Dorine

Hi @WK,

Are you using OutSystems Entities? If yes, You can rename the attribute "Cash" to "Amount" using OutSystems Service studio. Create a Static entity for "Type" and map the same with the first table. 

Share the screenshot or attach OML. So that other team members can provide alternate solutions.

Thanks, Aadhavan S

Hi WK,

can you share your oml, or a complete overview of your datamodel.  It's hard to try to help.


so if it would only be the table you describe, i would expect something like

select u.[Person], u.[Type], u.[Amount]
from {TableA}
unpivot
(
  Amount
  for Type in (Cash, Wire)
) u;

for example, you are referring to TableA in your select, I think you can only select from u (or in your case DUMMYUNPIVOT)


Dorine

Thank you @Dorine Boudry!

Seems so logical once done....

So basically this is how Query should look like:

Since all information needed for UNPIVOT is stored in TableA - UNPIVOT has been moved before the JOINS and all data coming from the UNPIVOT are referred to using its alias in the SELECT statement (obvious right now...).
After that JOINS are performed.

@Aadhavan Shanmugam answering your question - yes, all of these tables are OutSystems entities with relations:
TableB -TableA - One-To-Many,
TableD - TableC - One-To-Many

SELECT 
    DUMMYUNPIVOT.[FirstID],
    CONCAT('ABC', DATEPART(mm, {TableB}.[Date]),'_',DATEPART(yyyy, {TableB}.[Date])) AS DATE_ID,
    CAST( GETDATE() AS Date ) AS Example_DATE, 
    {TableC}.[ExampleVaue] AS EXAMPLE_VALUE,
    DUMMYUNPIVOT.[ExampleValue] AS EXAMPLE_VALUE2,
    REPLACE(CAST(AMOUNT AS VARCHAR(15)),',','.'),
    CASE EXAMPLE_TYPE
        WHEN 'EXAMPLE_AMOUNT' THEN 'TYPE1'
        WHEN 'EXAMPLE_AMOUNT2' THEN 'TYPE2'
        ELSE EXAMPLE_TYPE END AS EXAMPLE_TYPE
FROM {TableA}
UNPIVOT
(
    AMOUNT
    FOR EXAMPLE_TYPE IN ({TableA}.[EXAMPLE_AMOUNT], {TableA}.[EXAMPLE_AMOUNT2])
)AS DUMMYUNPIVOT
LEFT JOIN
{TableB}
ON
DUMMYUNPIVOT.[TABLEB_ID] = {TableB}.[Id]
LEFT JOIN 
{TableD}
ON 
{TableD}.[EXAMPLE_COLUMN] = DUMMYUNPIVOT.[EXAMPLE_COLUMN]
LEFT JOIN
{TableC}
ON 
{TableD}.[C_ID]={TableC}.[Id]
Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.