CODEWITHSUNDEEP
sqlsql-server
user2017513
user2017513

Reputation: 1

How to transpose a table and import into SQL Server table

I have the following table for a bicycle measurements which I have in a csv file & want to transpose & import into a table in my SQL Server database.

Size             XS S   M   L   XL
Frame Reach 365mm   385mm   405mm   425mm   445mm
Frame Stack 495mm   515mm   540mm   565mm   590mm
Stem Length 90mm    90mm    90mm    90mm    90mm
Seat Tube Length    505mm   525mm   551mm   577mm   603mm
Top Tube Length 481mm   504mm   530mm   557mm   582mm
B-B Drop             72mm   72mm    72mm    72mm    72mm
Chain-Stay Length   395mm   395mm   395mm   395mm   395mm
Seat-Tube Angle 77° 77° 77° 77° 77°
Head-Tube Angle 70.25°  71.5°   72° 72.5°   72.5°
Fork Rake            50mm   50mm    45mm    45mm    45mm
Trail            69mm   61mm    63mm    60mm    60mm
Front-Center    573mm   589mm   608mm   631mm   659mm
Wheelbase            957mm  973mm   992mm   1016mm  1043mm
Stand-Over Height   752mm   772mm   797mm   822mm   847mm
Head-Tube Length    99mm    115mm   139mm   163mm   189mm
Seat Post Setback   12.5mm  12.5mm  12.5mm  12.5mm  12.5mm
Seat-Post Length    350mm   350mm   350mm   350mm   350mm
Crank Length    167.5mm 170mm   172.5mm 172.5mm 175mm

So for the above table I want it to look like this:

Size    Frame Reach Frame Stack Stem Length -18° Horizonta  Seat Tube Length    Top Tube Length B-B Drop    Chain-Stay Length   Seat-Tube Angle Head-Tube Angle Fork Rake   Trail   Front-Center    Wheelbase   Stand-Over Height   Head-Tube Length    Seat Post Setback   Seat-Post Length    Crank Length
XS  365mm   495mm   90mm    505mm   481mm   72mm    395mm   77° 70.25°  50mm    69mm    573mm   957mm   752mm   99mm    12.5mm  350mm   167.5mm
S   385mm   515mm   90mm    525mm   504mm   72mm    395mm   77° 71.5°   50mm    61mm    589mm   973mm   772mm   115mm   12.5mm  350mm   170mm
M   405mm   540mm   90mm    551mm   530mm   72mm    395mm   77° 72° 45mm    63mm    608mm   992mm   797mm   139mm   12.5mm  350mm   172.5mm
L   425mm   565mm   90mm    577mm   557mm   72mm    395mm   77° 72.5°   45mm    60mm    631mm   1016mm  822mm   163mm   12.5mm  350mm   172.5mm
XL  445mm   590mm   90mm    603mm   582mm   72mm    395mm   77° 72.5°   45mm    60mm    659mm   1043mm  847mm   189mm   12.5mm  350mm   175mm
XL

I've found the pivot / unpivot function in this forum but but I couldn't quite get it to work for me. Please note, I've first imported the csv into a temp table where I've stripped out the "mm" & "°" from the data.

Any help would be greatly appreciated.

Upvotes: 0

Views: 648

Answers (1)

Dan
Dan

Reputation: 10680

For a one-time solution, I'd recommend opening the csv file in Excel, and then perform the transpose there.

If you really want to do it using T-SQL, you'd have to UNPIVOT the data first, and then PIVOT it back into the transposed layout, however, this only works if you use the same data type on your data columns ("float", for example). Say your temp table is called "Temp" and has the columns: "Dimension", "XS", "S", "M", "L" and "XL":

Unpivot:

SELECT Dimension, Size, Value
FROM Temp
UNPIVOT (Value FOR Size IN (XS, S, M, L, XL)) AS unpvt;

Put this output into a new temp table (temp2) and then use PIVOT like this:

SELECT Size, FrameReach, FrameStack, StemLength, SeatTubeLength, [...]
FROM Temp2
PIVOT (
    MAX(Value) FOR Dimension IN ('Frame Reach', 'Frame Stack', 'Stem Length', 'Seat Tube Length', [...])
    ) AS Pvt

Upvotes: 1

Related Questions