Rows to columns in SQL

1

Hello,

I am trying to change the layout of my SQL table from rows to columns base on ClassID. The below screenshots show the table and the output I wanted to have.

SCS
SCS2026×948 414 KB

Here is the script to create the PassID table and insert some values.

CREATE TABLE PassID
(
PassangerID VARCHAR(7),
ClassID VARCHAR(3),
Purchase Money,
Country VARCHAR(20),
Age SMALLINT
)

INSERT INTO PassID (PassangerID, ClassID, Purchase, Country, Age)
VALUES ('GIA2545','101','2250','USA','42'),('GIA2545','101','1525','CANADA','56'),('GIA2545','101','2550','USA','33'),('GIA2545','101','1500','JAPAN','47')
,('GIA2545','113','2250','CHINA','29'),('GIA2545','113','1500','CANADA','40')
,('GIA4020','120','1150','USA','55'),('GIA4020','120','2000','USA','67')
,('GIA1111','105','3000','CHINA','33'),('GIA1111','105','2000','JAPAN','47'),('GIA1111','105','2150','CANADA','31')
,('GIA1111','108','2500','CHINA','29')

Can anyone help me with this?

2

i tried till here

CREATE TABLE passid
  (
     passangerid VARCHAR(7),
     classid     VARCHAR(3),
     purchase    MONEY,
     country     VARCHAR(20),
     age         SMALLINT
  )

INSERT INTO passid
            (passangerid,
             classid,
             purchase,
             country,
             age)
VALUES      ('GIA2545',
             '101',
             '2250',
             'USA',
             '42'),
            ('GIA2545',
             '101',
             '1525',
             'CANADA',
             '56'),
            ('GIA2545',
             '101',
             '2550',
             'USA',
             '33'),
            ('GIA2545',
             '101',
             '1500',
             'JAPAN',
             '47'),
            ('GIA2545',
             '113',
             '2250',
             'CHINA',
             '29'),
            ('GIA2545',
             '113',
             '1500',
             'CANADA',
             '40'),
            ('GIA4020',
             '120',
             '1150',
             'USA',
             '55'),
            ('GIA4020',
             '120',
             '2000',
             'USA',
             '67'),
            ('GIA1111',
             '105',
             '3000',
             'CHINA',
             '33'),
            ('GIA1111',
             '105',
             '2000',
             'JAPAN',
             '47'),
            ('GIA1111',
             '105',
             '2150',
             'CANADA',
             '31'),
            ('GIA1111',
             '108',
             '2500',
             'CHINA',
             '29')

SELECT Row_number()
         OVER(
           partition BY passangerid, classid
           ORDER BY passangerid, classid) AS rn,
       *
INTO   passid_rn
FROM   passid

SELECT *
FROM   passid_rn a
       JOIN passid_rn b
         ON a.rn + 1 = b.rn
            AND a.passangerid = b.passangerid
            AND a.classid = b.classid
       JOIN passid_rn c
         ON c.passangerid = b.passangerid
            AND c.classid = b.classid
            AND b.rn + 1 = c.rn
       JOIN passid_rn d
         ON c.passangerid = d.passangerid
            AND c.classid = d.classid
            AND c.rn + 1 = d.rn

DROP TABLE passid

DROP TABLE passid_rn

image
image1863×71 5.17 KB

3

Hi harishgg1, Thank you for trying to help me but I tried and it removed other records at the end because of rn joing.

4

i will have to DEEP DEEP Dive ..

i will try later .. please excuse me .. busy with other things

:love_you_gesture:

1 Like
5

thank you harishgg1, really appreciate with your help and time.

6

The first problem is how you want to identify which one is first in the group. If you have some other column(s) available to determine the order then you can use that in the generation of the row number.

I used the purchase amount - from smallest to largest such that the largest amount will be the last item.

Declare @passid table (PassangerID varchar(7)
                     , ClassID varchar(3)
                     , Purchase money
                     , Country varchar(20)
                     , Age smallint);

 Insert Into @passid (PassangerID, ClassID, Purchase, Country, Age)
 Values ('GIA2545', '101', '2250', 'USA', '42')
      , ('GIA2545', '101', '1525', 'CANADA', '56')
      , ('GIA2545', '101', '2550', 'USA', '33')
      , ('GIA2545', '101', '1500', 'JAPAN', '47')
      , ('GIA2545', '113', '2250', 'CHINA', '29')
      , ('GIA2545', '113', '1500', 'CANADA', '40')
      , ('GIA4020', '120', '1150', 'USA', '55')
      , ('GIA4020', '120', '2000', 'USA', '67')
      , ('GIA1111', '105', '3000', 'CHINA', '33')
      , ('GIA1111', '105', '2000', 'JAPAN', '47')
      , ('GIA1111', '105', '2150', 'CANADA', '31')
      , ('GIA1111', '108', '2500', 'CHINA', '29');

   With rowData
     As (
 Select *
      , rn = row_number() over(Partition By p.PassangerID, p.ClassID Order By p.Purchase)
   From @passid                         p
        )
 Select PassangerID
      , ClassID
      , Purchase_1 = max(Case When rn = 1 Then Purchase End)
      , Country_1  = max(Case When rn = 1 Then Country End)
      , Age_1      = max(Case When rn = 1 Then Age End)
      , Purchase_2 = max(Case When rn = 2 Then Purchase End)
      , Country_2  = max(Case When rn = 2 Then Country End)
      , Age_2      = max(Case When rn = 2 Then Age End)
      , Purchase_3 = max(Case When rn = 3 Then Purchase End)
      , Country_3  = max(Case When rn = 3 Then Country End)
      , Age_3      = max(Case When rn = 3 Then Age End)
      , Purchase_4 = max(Case When rn = 4 Then Purchase End)
      , Country_4  = max(Case When rn = 4 Then Country End)
      , Age_4      = max(Case When rn = 4 Then Age End)
   From rowData
  Group By
        PassangerID
      , ClassID;

If there can be a variable number - then you either need to determine a fixed number and always calculated for that fixed number, or you need to use dynamic SQL I would recommend setting up and returning a fixed number instead of using a dynamic approach.

1 Like
7

Thank you jeffw8713, let me try it. Appreciate your time and help, Jeff

8

It is working, Thanks Jeff

9

You are welcome - glad to have been able to help.