CODEWITHSUNDEEP
sql-serverpivot
tvviewer
tvviewer

Reputation: 3

Turn SQL rows into columns

i have imported lots of building-data from another system into MS SQL 2017 but facing a problem turning rows into columns. Most fields are var-chars in the system imported from and a field can have leading/trailing spaces. Believe me when i say, i have searched many places/pages for an answer.

data:
key1   Unit       Measurement 
04A    Unit 1     156,8000
04A    Unit 2     5.845,3000
678R   Unit 1     8,6000
678R   Unit 2     409,6000
678R   Unit 3     17

how can data be turned into:
key1   Unit 1     Unit 2       Unit 3
04A    156,8000   5.845,3000   NULL
678R   8,6000     409,6000     17

measurement column - should this be float or decimal - we are scandinavians here :-) ?

i have tried pivot with max as aggregate function but too many rows appear with nulls in Unit 1 , 2 , 3 - it even rejects rows having value

Upvotes: 0

Views: 81

Answers (3)

StepUp
StepUp

Reputation: 38094

You can use PIVOT operator:

SELECT * 
FROM 
(
    SELECT * FROM YourTable
)AS FooData
PIVOT(
    AVG(FooData.Measurement)
    FOR Unit IN ([Unit 1], [Unit 2], [Unit 3])
) AS FooPivot

An example:

DECLARE @tbl TABLE
(
   key1   varchar(50),
   Unit   VARCHAR(50),
   Measurement DECIMAL(18,8)
)

INSERT INTO @tbl
(
    key1,
    Unit,
    Measurement
)
VALUES
  ('04A', 'Unit 1',     156.8000)
, ('04A', 'Unit 2',     5.8453000)
, ('678R', 'Unit 1',     8.6000)
, ('678R', 'Unit 2',     409.6000)
, ('678R', 'Unit 3',     17)
SELECT * 
FROM 
(
    SELECT * FROM @tbl
)AS FooData
PIVOT(
    AVG(FooData.Measurement)
    FOR Unit IN ([Unit 1], [Unit 2], [Unit 3])
) AS FooPivot

OUTPUT:

key1    Unit 1            Unit 2        Unit 3
04A     156.80000000    5.84530000      NULL
678R    8.60000000      409.60000000    17.00000000

UPDATE:

I don't know what actual data type of Measurement field which has value 5.845,3000, however I strongly believe that this data is type of numeric. If yes, then it is better to store numerical data as a numerical type as it is responsibility of the presentation layer cause there are different requirements to show numbers in various countries. To add commas you can use pipes at the client side.

In addition, if Measurement is type of number and you store it as varchar(50), then you would get problems while ordering. So it is better to store as numeric.

Otherwise, you can use datatype varchar(255):

DECLARE @tbl TABLE
(
   key1   varchar(50),
   Unit   VARCHAR(50),
   Measurement VARCHAR(255)
)

INSERT INTO @tbl
(
    key1,
    Unit,
    Measurement
)
VALUES
  ('04A', 'Unit 1',     '156.8000')
, ('04A', 'Unit 2',     '5,845.3000')
, ('678R', 'Unit 1',     '8.6000')
, ('678R', 'Unit 2',     '409.6000')
, ('678R', 'Unit 3',     '17')
SELECT * 
FROM 
(
    SELECT * FROM @tbl
)AS FooData
PIVOT(
    MAX(FooData.Measurement)
    FOR Unit IN ([Unit 1], [Unit 2], [Unit 3])
) AS FooPivot

Upvotes: 1

EzLo
EzLo

Reputation: 14189

Make sure to limit the pivot's column set before actually applying it:

;WITH PrePivot AS -- Limit the columns for the PIVOT's GROUP BY
(
    SELECT
        Key1,
        Unit,
        Measurement
    FROM
        YourTable
)
SELECT
    P.Key1,
    P.[Unit 1],
    P.[Unit 2],
    P.[Unit 3]
FROM
    PrePivot AS T
    PIVOT (
        MAX(T.Measurement) FOR T.Unit IN ([Unit 1], [Unit 2], [Unit 3])
    ) AS P

Please check this post to see how PIVOT actually does an implicit GROUP BY with all non-pivoting columns.

Upvotes: 0

Suraj Kumar
Suraj Kumar

Reputation: 5643

You can try this using PIVOT. Here you need to use MAX() function rather than AVG() or SUM() as data saved is in varchar.

create table #temp(key1 varchar(10), Unit Varchar(20), Measurement varchar(20))
insert into #temp values
('04A', 'Unit 1', '156,8000'),
('04A', 'Unit 2', '5.845,3000'),
('678R', 'Unit 1', '8,6000'),
('678R', 'Unit 2', '409,6000'),
('678R', 'Unit 3', '17')

SELECT * 
FROM 
(
    SELECT * FROM #temp
)AS FooData
PIVOT(
    MAX(FooData.Measurement)
    FOR Unit IN ([Unit 1], [Unit 2], [Unit 3])
) AS FooPivot

The output is as shown below:

key1    Unit 1      Unit 2      Unit 3
04A     156,8000    5.845,3000  NULL
678R    8,6000      409,6000    17

Upvotes: 1

Related Questions