Break Down A Table To Pivot In Columns (sql,pyspark)

I'm working in an environment pyspark with python3.6 in AWS Glue. I have this table : +----+-----+-----+-----+ |year|month|total| loop| +----+-----+-----+-----+ |2012| 1| 20|l

Solution 1:

Table Script and Sample data

CREATETABLE [TableName](
    [year] [nvarchar](50) NULL,
    [month] [int] NULL,
    [total] [int] NULL,
    [loop] [nvarchar](50) NULL
) 

INSERT [TableName] ([year], [month], [total], [loop]) VALUES (N'2012', 1, 20, N'loop1')
INSERT [TableName] ([year], [month], [total], [loop]) VALUES (N'2012', 2, 30, N'loop1')
INSERT [TableName] ([year], [month], [total], [loop]) VALUES (N'2012', 1, 10, N'loop2')
INSERT [TableName] ([year], [month], [total], [loop]) VALUES (N'2012', 2, 5, N'loop2')
INSERT [TableName] ([year], [month], [total], [loop]) VALUES (N'2012', 1, 50, N'loop3')
INSERT [TableName] ([year], [month], [total], [loop]) VALUES (N'2012', 2, 60, N'loop3')

Using Pivot function...

SELECT * 
FROM   TableName
       PIVOT(Max([total]) 
            FOR [loop] IN ([loop1], [loop2], [loop3]) ) pvt

Online Demo: http://www.sqlfiddle.com/#!18/164a4/1/0

If you are looking for a dynamic solution, then try this... (Dynamic Pivot)

DECLARE@colsAS NVARCHAR(max) = Stuff((SELECTDISTINCT','+ Quotename([loop])
         FROM   TableName
         FOR xml path(''), type).value('.', 'NVARCHAR(MAX)'), 1, 1, ''); 

DECLARE@queryAS NVARCHAR(max) ='SELECT * 
                                    FROM   TableName
                                           PIVOT(Max([total]) 
                                                FOR [loop] IN ('+@cols+') ) pvt';

EXECUTE(@query) 

Online Demo: http://www.sqlfiddle.com/#!18/164a4/3/0

Output

+------+-------+-------+-------+-------+|year|month| loop1 | loop2 | loop3 |+------+-------+-------+-------+-------+|2012|1|20|10|50||2012|2|30|5|60|+------+-------+-------+-------+-------+

Solution 2:

You don't need to use join you can do conditional aggregation:

selectyear, month,
       max(casewhen loop ='loop1'then total end) loop1,
       max(casewhen loop ='loop2'then total end) loop2,
       max(casewhen loop ='loop3'then total end) loop3
from test a
groupbyyear, month;

Solution 3:

You can use PIVOT() to convert rows to columns:

SELECT
    year,
    MONTH,
    p.loop1 AS'total_loop1',
    p.loop2 AS'total_loop2',
    p.loop3 AS'total_loop3'FROM
    tablename
    PIVOT
        (MAX(total)
            FORloopIN ([loop1], [loop2], [loop3])
        ) AS p;