Sql server complex select query row as column



  • I have two tables in SQL Server:

    Table1:

    Table1

    Table2:

    Table2

    I need a select query to give me output like this:

    Required Output

    I am attaching here the sample data of two tables in text format since I am unable to add the Excel sheets here

    Table1

    SCHOOL_ID TEACHER_ID IS_HEAD_MASTER TAGGED_CLASS IMAGE_NAME LATITUDE LONGITUDE ACCURACY CREATED_DATE CREATED_BY AI_IMAGE_NAME AI_STATUS IS_DOWNLOADED DOWNLOADED_DATE IS_PROCESSEED
    204 341 Y 10.jpeg 1.1 2.1 80 49:33.0 28110100204 NULL NULL NULL 16:54.0 NULL
    204 341 Y 20.jpeg 1.1 2.1 80 49:33.0 28110100204 NULL NULL NULL 16:54.0 NULL
    204 341 Y 30.jpeg 1.1 2.1 80 49:33.0 28110100204 NULL NULL NULL 16:54.0 NULL
    204 734 N 40.jpeg 1.2 2.2 90 55:59.3 28110100204 NULL NULL NULL 16:54.0 NULL
    204 734 N 50.jpeg 1.2 2.2 90 55:59.3 28110100204 NULL NULL NULL 16:54.0 NULL
    204 734 N 60.jpeg 1.2 2.2 90 55:59.3 28110100204 NULL NULL NULL 16:54.0 NULL

    Table2

    TEACHER_ID TAGGED_SCHOOL_ID ATTENDANCE_DT ATTENDANCE_TYPE IMAGE_NAME LATITUDE LONGITUDE ACCURACY CAPTURED_TIME AI_IMAGE_NAME AI_STATUS IS_DOWNLOADED DOWNLOADED_DATE IS_PROCESSEED
    341 204 21/02/2022 IN 1.jpeg 1.1 2.1 80 37:16.0 NULL NULL NULL NULL NULL
    341 204 21/02/2022 OUT 2.jpeg 1.2 2.2 80 55:45.0 NULL NULL NULL NULL NULL
    734 204 21/02/2022 IN 3.jpeg 1.3 2.3 80 24:24.0 NULL NULL NULL NULL NULL
    734 204 21/02/2022 OUT 4.jpeg 1.4 2.4 80 31:47.0 NULL NULL NULL NULL NULL

    I have written below query but it is not giving the output in required format:

    select t1.TEACHER_ID as "t1.TEACHER_ID", t2.TEACHER_ID as "t2.TEACHER_ID", 
    STRING_AGG(t1.IMAGE_NAME,', ') as "t1.IMAGE_NAME", STRING_AGG(t2.IMAGE_NAME,', ') as 
    "t2.IMAGE_NAME" from table1 t1, table2 t2 where t1.TEACHER_ID=t2.TEACHER_ID GROUP BY 
    t1.TEACHER_ID=t2.TEACHER_ID;
    

    Please help me to solve my query issue.

    It's part of my ML work. Once I will get the records in required format, I will use the images in further process. Currently I am struggling with select query part.

    Table1 and Table2 are just dummy names. Obviously, the actual table names and data are different.



  • Given data:

    DECLARE @Table1 table 
    (
        TeacherID integer NOT NULL, 
        ImageName varchar(20) NOT NULL
    );
    

    DECLARE @Table2 table
    (
    TeacherID integer NOT NULL,
    ImageName varchar(20) NOT NULL
    );

    INSERT @Table1
    (TeacherID, ImageName)
    VALUES
    (341, '10.jpeg'),
    (341, '20.jpeg'),
    (341, '30.jpeg'),
    (734, '40.jpeg'),
    (734, '50.jpeg'),
    (734, '60.jpeg');

    INSERT @Table2
    (TeacherID, ImageName)
    VALUES
    (341, '1.jpeg'),
    (341, '2.jpeg'),
    (734, '3.jpeg'),
    (734, '4.jpeg');

    The basic idea is:

    WITH 
        CombinedData AS
        (
            -- Get data from both sources
            SELECT T1.TeacherID, T1.ImageName
            FROM @Table1 AS T1
            UNION ALL
            SELECT T2.TeacherID, T2.ImageName
            FROM @Table2 AS T2
        ),
        ImageNumbers AS
        (
            -- Extract just the numbers from the ImageName
            -- for ordering purposes next
            SELECT
                CD.TeacherID, 
                CD.ImageName,
                ImageNumber = CONVERT(integer, 
                    REPLACE(CD.ImageName, '.jpeg', ''))
            FROM CombinedData AS CD
        ),
        NumberedRows AS
        (
            -- Number the rows for each TeacherID
            -- in ImageNumber order
            -- These will be column numbers after the pivot
            SELECT 
                I.TeacherID, 
                I.ImageName,
                RowNum = ROW_NUMBER() OVER (
                    PARTITION BY I.TeacherID
                    ORDER BY I.ImageNumber)
            FROM ImageNumbers AS I
        )
    -- Pivot the data
    SELECT
        P.TeacherID, 
        Image1 = P.[1],
        Image2 = P.[2], 
        Image3 = P.[3], 
        Image4 = P.[4], 
        Image5 = P.[5]
    FROM NumberedRows
    PIVOT 
    (
        MAX(ImageName) 
        FOR RowNum IN ([1], [2], [3], [4], [5])
    ) AS P;
    

    Results:

    TeacherID Image1 Image2 Image3 Image4 Image5
    341 1.jpeg 2.jpeg 10.jpeg 20.jpeg 30.jpeg
    734 3.jpeg 4.jpeg 40.jpeg 50.jpeg 60.jpeg

    https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=8f13a1d6fcc49e56e1ef8dcd800639c5

    If you need a dynamic pivot because you don't know in advance how many columns there will be, see:

    • https://dba.stackexchange.com/q/63698/1192
    • https://dba.stackexchange.com/q/31760/1192

Log in to reply
 


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2