SQL Server How to insert other column on pivot?

sqxo8psd 于 2023-05-28 发布在其他

关注(0)|答案(1)|浏览(118)

I am trying to make report, since it's take too long on website so I build the query, but I can't insert other column on pivot.

On example data each training have different names, score and time @user

create table TBL_USER (
  ID int IDENTITY(1,1) PRIMARY KEY,
  USERNAME varchar(50),
);
create table TBL_TRAINING(
  ID int IDENTITY(1,1) PRIMARY KEY,
  NM_TRAINING varchar(255),
);
create table TBL_DETAIL_TRAINING(
  ID_DETAIL int IDENTITY(1,1) PRIMARY KEY,
  ID_TRAINING int,
  ID_USER varchar(50),
  SCORE int,
  TIME time,
);

INSERT INTO TBL_USER VALUES ('AA')
INSERT INTO TBL_USER VALUES ('BB')
INSERT INTO TBL_USER VALUES ('CC')

INSERT INTO TBL_TRAINING VALUES ('T_1')
INSERT INTO TBL_TRAINING VALUES ('T_2')
INSERT INTO TBL_TRAINING VALUES ('T_3')

INSERT INTO TBL_DETAIL_TRAINING VALUES ('1','1','80','00:50:00')
INSERT INTO TBL_DETAIL_TRAINING VALUES ('1','2','40','00:40:00')
INSERT INTO TBL_DETAIL_TRAINING VALUES ('1','3','60','00:10:00')
INSERT INTO TBL_DETAIL_TRAINING VALUES ('2','1','30','00:30:00')
INSERT INTO TBL_DETAIL_TRAINING VALUES ('2','2','40','00:50:00')
INSERT INTO TBL_DETAIL_TRAINING VALUES ('2','3','50','00:30:00')
INSERT INTO TBL_DETAIL_TRAINING VALUES ('3','1','100','00:50:00')
INSERT INTO TBL_DETAIL_TRAINING VALUES ('3','2','80','00:50:00')
INSERT INTO TBL_DETAIL_TRAINING VALUES ('3','3','70','00:30:00')

Sample Data
| ID_USER | Username | Name_Training | Score | Time     |
|---------|----------|---------------|-------|----------|
| 1       | AA       | T_1           | 80    | 00:50:00 |
| 2       | BB       | T_1           | 40    | 00:40:00 |
| 3       | CC       | T_1           | 60    | 00:10:00 |
| 1       | AA       | T_2           | 30    | 00:30:00 |
| 2       | BB       | T_2           | 40    | 00:50:00 |
| 3       | CC       | T_2           | 50    | 00:30:00 |
| 1       | AA       | T_3           | 100   | 00:50:00 |
| 2       | BB       | T_3           | 80    | 00:50:00 |
| 3       | CC       | T_3           | 70    | 00:30:00 |

Expected results
| ID_USER | Username | T_1 | Time_T_1 | T_2 | Time_T_2 | T_3 | Time_T_3 |
|---------|----------|-----|----------|-----|----------|-----|----------|
| 1       | AA       | 80  | 00:50:00 | 30  | 00:30:00 | 100 | 00:50:00 |
| 2       | BB       | 40  | 00:40:00 | 40  | 00:50:00 | 80  | 00:50:00 |
| 3       | CC       | 60  | 00:10:00 | 50  | 00:30:00 | 70  | 00:30:00 |

declare @idtraining as nvarchar (max)
declare @ScriptPivot as nvarchar (max)
declare @ScriptFinal as nvarchar (max)

// GET ALL NAME TRAINING FOR PIVOT
select @ScriptPivot =   SELECT STUFF((SELECT ',' + '['+CAST(NM_TRAINING AS NVARCHAR(MAX))+']'    
                    FROM TBL_TRAINING
                    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') ,1,1,'') 

set @ScriptFinal = SELECT * FROM (
    SELECT C.ID, C.USERNAME, A.NM_TRAINING, B.SCORE, B.TIME 
    FROM TBL_TRAINING A
    JOIN TBL_DETAIL_TRAINING B ON B.ID_TRAINING = A.ID
    JOIN TBL_USER C ON C.ID = B.ID_USER 
    -- LOT JOIN HERE
) AS SOURCETABLE PIVOT (MAX(SCORE) FOR NM_TRAINING IN ([T_1],[T_2],[T_3])) AS PIVOTTABLE

--print @ScriptFinal

So far I only know add 1 column (training or time).

I can add another column time by adding pivot script again but it is added at the last column and I get more than 100 training names.

I tried using a temp table then using a pivot but I can't figure out how to insert the column times on the list column. Is there a way to get the same result?

sql-server

来源：https://stackoverflow.com/questions/76323120/how-to-insert-other-column-on-pivot

1条答案

按热度按时间

xzv2uavs1#

*Not a complete answer, but you can work from this.

I still don't understand the use of @idtraining .

I added more training so you can see how dynamically added as columns.

CREATE TABLE UserTraining (
    ID_USER INTEGER,
    Username VARCHAR(255),
    Training VARCHAR(255),
    Score INTEGER,
    Time TIME
);

INSERT INTO UserTraining (ID_USER, Username, Training, Score, Time) VALUES
    (1, 'AA', 'T_1', 80, '00:50:00'),
    (2, 'BB', 'T_1', 40, '00:40:00'),
    (3, 'CC', 'T_1', 60, '00:10:00'),
    (1, 'AA', 'T_2', 30, '00:30:00'),
    (2, 'BB', 'T_2', 40, '00:50:00'),
    (3, 'CC', 'T_2', 50, '00:30:00'),
      (3, 'CC', 'T_7', 50, '00:30:00'),
    (1, 'AA', 'T_3', 100, '00:50:00'),
    (2, 'BB', 'T_3', 80, '00:50:00'),
      (2, 'BB', 'T_5', 80, '00:50:00'),
    (3, 'CC', 'T_3', 70, '00:30:00');


DECLARE @pivotColumns NVARCHAR(MAX),
        @sql NVARCHAR(MAX)

-- Get the distinct training names for Score and Time
SELECT @pivotColumns = STUFF((SELECT DISTINCT ', ' + QUOTENAME(Training + '_Score') + ', ' + QUOTENAME(Training + '_Time')
                               FROM UserTraining 
                               FOR XML PATH('')), 1, 2, '')

-- Build the dynamic pivot query
SET @sql = '
    WITH ScoreTable AS (
        SELECT ID_USER, Username, Training + ''_Score'' AS Training, CAST(Score AS NVARCHAR) AS Value
        FROM UserTraining
    ), 
    TimeTable AS (
        SELECT ID_USER, Username, Training + ''_Time'' AS Training, CAST(Time AS NVARCHAR) AS Value
        FROM UserTraining
    )
    SELECT ID_USER, Username, ' + @pivotColumns + '
    FROM (
        SELECT * FROM ScoreTable
        UNION ALL
        SELECT * FROM TimeTable
    ) AS SourceTable
    PIVOT (
        MAX(Value)
        FOR Training IN (' + @pivotColumns + ')
    ) AS PivotTable
    ORDER BY ID_USER'

EXECUTE sp_executesql @sql

ID_USER	Username	T_1_Score	T_1_Time	T_2_Score	T_2_Time	T_3_Score	T_3_Time	T_5_Score	T_5_Time	T_7_Score	T_7_Time
1	AA	80	00:50:00.0000000	30	00:30:00.0000000	100	00:50:00.0000000	null	null	null	null
2	BB	40	00:40:00.0000000	40	00:50:00.0000000	80	00:50:00.0000000	80	00:50:00.0000000	null	null
3	CC	60	00:10:00.0000000	50	00:30:00.0000000	70	00:30:00.0000000	null	null	50	00:30:00.0000000

fiddle

This is the resulting dynamic query:

WITH ScoreTable AS (
        SELECT ID_USER, Username, Training + '_Score' AS Training, CAST(Score AS NVARCHAR) AS Value
        FROM UserTraining
    ), 
    TimeTable AS (
        SELECT ID_USER, Username, Training + '_Time' AS Training, CAST(Time AS NVARCHAR) AS Value
        FROM UserTraining
    )
    SELECT ID_USER, Username, [T_1_Score], [T_1_Time], [T_2_Score], [T_2_Time], [T_3_Score], [T_3_Time], [T_5_Score], [T_5_Time], [T_7_Score], [T_7_Time]
    FROM (
        SELECT * FROM ScoreTable
        UNION ALL
        SELECT * FROM TimeTable
    ) AS SourceTable
    PIVOT (
        MAX(Value)
        FOR Training IN ([T_1_Score], [T_1_Time], [T_2_Score], [T_2_Time], [T_3_Score], [T_3_Time], [T_5_Score], [T_5_Time], [T_7_Score], [T_7_Time])
    ) AS PivotTable
    ORDER BY ID_USER

赞(0）回复(0）举报 2023-05-28

我来回答

SQL Server How to insert other column on pivot?

1条答案

相关问题

热门标签

最新问答