CODEWITHSUNDEEP
sql-server
Dale Arp
Dale Arp

Reputation: 21

I need to rollup columns data into a single row

I had a need to transpose a given column into multiple added columns using a view, placing the value of a second column into the appropriate added column, then having only one value across all the added columns for a given record. Column A and B are the columns to group by. Added columns C - F now have one text value per record (number as a varchar). I now want to group the records to one record, now where every added column holds one value for the grouping. Perhaps a complication is that one or more of the added columns may have all NULL values for a given grouping. I know how to do this if numberic values, then summing and grouping, but these are numbers as varchar. I have not been able to apply any of the 'Similar questions'

CREATE TABLE Transposed(
    enterdate DATETIME NOT NULL,
    Area VARCHAR(50) NOT NULL,
    ColC VARCHAR(50) NOT NULL,
    ColD VARCHAR(50) NOT NULL,
    ColE VARCHAR(50) NOT NULL,
    ColF VARCHAR(50) NOT NULL
)
INSERT INTO Transposed
VALUES
 ('1/1/2021', 'A', '25', '', '', '')
,('1/1/2021', 'A', '', '35', '', '')
,('1/1/2021', 'A', '', '', '', '')
,('1/1/2021', 'A', '', '', '', '45')

Select * From Transposed

Where the result can be reduced to one line:

1/1/2021,   A,      25,      35,        Null,      45

Upvotes: 0

Views: 351

Answers (1)

eshirvana
eshirvana

Reputation: 24603

one way :

select Area 
    ,enterdate 
    , max(ColC) as ColC
    , max(ColD) as ColD
    , ...
from Transposed
group by Area,enterdate

Upvotes: 2

Related Questions