Write alias for columns with same names from multiple pivots in snowflake

Question

My query is

SELECT *

FROM  (

WITH as1 AS (
SELECT
    DISTINCT a.COMPANY_NAME,
    b.industry_sector,
    b.INDUSTRY_GROUP,
    c.VERTICAL,
    D.VCROUND,
    D.VCROUND AS VCROUND1,
    D.VCROUND AS VCROUND2,
    D.VCROUND AS VCROUND3,
    D.VCROUND AS VCROUND4,
    D.Deal_date,
    e.EMPLOYEE_COUNT ,
    a.REVENUE,
    a.TOTALRAISED ,
    D.Premoney_Valuation
FROM
    DAT.PIK.PB_C a
LEFT JOIN D.PIT.PB_COMPANY_INDUS b ON
    a.C_ID = b.C_ID
LEFT JOIN D.P.PB_COMPANY_V c ON
    c.C_ID = b.C_ID
    AND a.C_ID = c.C_ID
LEFT JOIN DA.PIT.PB_DEAL D ON
    D.C_ID = c.C_ID
    AND D.c_id = b.c_id
    AND D.C_ID = a.c_id
LEFT JOIN DA.PITCHBOOK.PB_COMP e ON
    e.C_ID = D.C_ID
    AND e.C_ID = c.C_ID
    AND e.C_ID = b.C_ID
    AND e.C_ID = a.C_ID
     where a.COMPANY_NAME ='11Sight'
                                
)
                                
SELECT
    *
FROM
    as1
    pivot(sum(TOTALRAISED) FOR VCROUND IN ('Angel', '1st Round', '2nd Round', '3rd Round', '4th Round', '5th Round')) AS p (
                                                                                                COMPANY_NAME,industry_sector,INDUSTRY_GROUP,VERTICAL,Angel_EmployeeCount,
                                                                                                R1_EmployeeCount,R2_EmployeeCount,R3_EmployeeCount,
                                                                                                R4_EmployeeCount,R5_EmployeeCount)
     pivot(sum(EMPLOYEE_COUNT) FOR VCROUND1 IN ('Angel', '1st Round', '2nd Round', '3rd Round', '4th Round', '5th Round')) 
     AS q 
    

     
    ).

I am getting 'Angel', '1st Round', '2nd Round', '3rd Round', '4th Round', '5th Round' , . Can you please tell me how to differentiate between columns from top pivot to bottom pivot which gives same columns.

score 2 · Accepted Answer · answered Jan 20 '22 at 05:38

so with this pared down SQL and fake_data:

WITH fake_data AS (
    SELECT *, VCROUND AS VCROUND1 FROM VALUES
        ('comp1', 'ind_1', 'group_1', 'virt_1', 'Angel', 1, 1000),
        ('comp1', 'ind_1', 'group_1', 'virt_1', '1st Round', 10, 100000),
        ('comp1', 'ind_1', 'group_1', 'virt_1', '2nd Round', 11, 200000),
        ('comp1', 'ind_1', 'group_1', 'virt_1', '3rd Round', 12, 300000)
        v(COMPANY_NAME, industry_sector, INDUSTRY_GROUP, VERTICAL, VCROUND, EMPLOYEE_COUNT, TOTALRAISED)
)                            
SELECT *
FROM fake_data fd
    pivot(sum(TOTALRAISED) FOR VCROUND IN ('Angel', '1st Round', '2nd Round', '3rd Round')) AS p 
    (
        COMPANY_NAME,
        industry_sector,
        INDUSTRY_GROUP,
        VERTICAL,
        Angel_EmployeeCount,
        R1_EmployeeCount,
        R2_EmployeeCount,
        R3_EmployeeCount,
        R4_EmployeeCount,
        R5_EmployeeCount
    )
    pivot(sum(EMPLOYEE_COUNT) FOR VCROUND1 IN ('Angel', '1st Round', '2nd Round', '3rd Round')) 
     AS q ;

I get:

COMPANY_NAME	INDUSTRY_SECTOR	INDUSTRY_GROUP	VERTICAL	"'Angel'"	"'1st Round'"	"'2nd Round'"	"'3rd Round'"	'Angel'	'1st Round'	'2nd Round'	'3rd Round'
comp1	ind_1	group_1	virt_1	1,000				1
comp1	ind_1	group_1	virt_1		100,000				10
comp1	ind_1	group_1	virt_1			200,000				11
comp1	ind_1	group_1	virt_1				300,000				12

so agree it's confusing..

So moving away from PIVOT as it's not adding value, lets just write it by hand as:

WITH fake_data AS (
    SELECT * FROM VALUES
        ('comp1', 'ind_1', 'group_1', 'virt_1', 'Angel', 1, 1000),
        ('comp1', 'ind_1', 'group_1', 'virt_1', '1st Round', 10, 100000),
        ('comp1', 'ind_1', 'group_1', 'virt_1', '2nd Round', 11, 200000),
        ('comp1', 'ind_1', 'group_1', 'virt_1', '3rd Round', 12, 300000)
        v(company_name, industry_sector, industry_group, vertical, vcround, employee_count, totalraised)
)  
SELECT 
    company_name,
    industry_sector,
    industry_group,
    vertical,
    SUM(IFF(vcround='Angel', totalraised, null)) AS angel_totalraised,
    SUM(IFF(vcround='1st Round', totalraised, null)) AS r1_totalraised,
    SUM(IFF(vcround='2nd Round', totalraised, null)) AS r2_totalraised,
    SUM(IFF(vcround='3rd Round', totalraised, null)) AS r3_totalraised,

    SUM(IFF(vcround='Angel', EMPLOYEE_COUNT, null)) AS angel_employeecount,
    SUM(IFF(vcround='1st Round', EMPLOYEE_COUNT, null)) AS r1_employeecount,
    SUM(IFF(vcround='2nd Round', EMPLOYEE_COUNT, null)) AS r2_employeecount,
    SUM(IFF(vcround='3rd Round', EMPLOYEE_COUNT, null)) AS r3_employeecount
FROM fake_data
GROUP BY 1,2,3,4;

gives:

COMPANY_NAME	INDUSTRY_SECTOR	INDUSTRY_GROUP	VERTICAL	"'Angel'"	"'1st Round'"	"'2nd Round'"	"'3rd Round'"	'Angel'	'1st Round'	'2nd Round'	'3rd Round'
comp1	ind_1	group_1	virt_1	1,000	100,000	200,000	300,000	1	10	11	12

which is more of what I think you are looking for.

Write alias for columns with same names from multiple pivots in snowflake

1 Answers1