T-SQL donne des colonnes

J'ai une table (t1) comme ci-dessous

Id Name RelId 1 a 2 2 b 3 3 c 4 4 d 3 5 e 6

L'autre table (t2)

 Id data FK Order 1 aa 2 2 2 bb 2 3 3 cc 2 1 4 dd 2 4 5 ee 2 5 6 ff 3 3 7 gg 3 2 8 hh 3 1 9 ii 4 7 10 jj 4 4 11 kk 4 1 12 ll 4 3 13 mm 6 1 14 nn 6 2 15 oo 6 3 16 pp 6 4

Mon résultat de sortie est à la search de

 +----+------+-------+-------+------+----------+ | id | name | RelId | Col 1 | Col2 | Col-Oth | +----+------+-------+-------+------+----------+ | 1 | a | 2 | cc | aa | bb,dd,ee | | 2 | b | 3 | hh | gg | ff | | 3 | c | 4 | kk | ll | jj,ii | | 4 | d | 3 | hh | gg | ff | | 5 | e | 6 | mm | nn | oo,pp | +----+------+-------+-------+------+----------+

sur la base de la table Relid dans T1 joindre à la colonne FK dans T2 et peupler col1 avec datatables de command les less, col2 avec datatables d'ordre supérieur suivant et col-oth avec datatables séparées par des virgules séparées.

Besoin de votre aide sur le même.

 SELECT id,name,RelId, (select data,rownumber() (partition by data order by order asc) from t2 inner join t1 on t1.relid= t2.FK) from t1

Essayez la requête suivante:

 DECLARE @TEMP TABLE ( Id INT, Name VARCHAR(10), RelId INT ) INSERT INTO @TEMP VALUES (1,'a',2),(2,'b',3),(3,'c',4),(4,'d',3),(5,'e',6) DECLARE @TEMP1 TABLE ( Id INT, Data varchar(10), FK INT, [order] INT ) INSERT INTO @TEMP1 VALUES (1 ,'aa',2,2),(2 ,'bb',2,3),(3 ,'cc',2,1),(4 ,'dd',2,4),(5 ,'ee',2,5), (6 ,'ff',3,3),(7 ,'gg',3,2),(8 ,'hh',3,1),(9 ,'ii',4,7),(10,'jj',4,4), (11,'kk',4,1),(12,'ll',4,3),(13,'mm',6,1),(14,'nn',6,2),(15,'oo',6,3),(16,'pp',6,4) SELECT t1.*, (SELECT Data FROM (SELECT ROW_NUMBER() OVER(ORDER BY t2.[order]) As RowNo,Data FROM @TEMP1 t2 WHERE t2.FK = t1.RelId)t3 WHERE t3.RowNo=1), (SELECT Data FROM (SELECT ROW_NUMBER() OVER(ORDER BY t2.[order]) As RowNo,Data FROM @TEMP1 t2 WHERE t2.FK = t1.RelId)t3 WHERE t3.RowNo=2), STUFF((SELECT DISTINCT ',' + Data FROM (SELECT ROW_NUMBER() OVER(ORDER BY t2.[order]) As RowNo,Data FROM @TEMP1 t2 WHERE t2.FK = t1.RelId)t3 WHERE t3.RowNo > 2 FOR XML PATH ('')), 1, 1, '') FROM @TEMP t1

Utilisation de PIVOT :

 DECLARE @t1 TABLE ( ID INT , Name CHAR(1) , RelID INT ) DECLARE @t2 TABLE ( ID INT , Data CHAR(2) , RelID INT , Ordering INT ) INSERT INTO @t1 VALUES ( 1, 'a', 2 ), ( 2, 'b', 3 ), ( 3, 'c', 4 ), ( 4, 'd', 3 ), ( 5, 'e', 6 ) INSERT INTO @t2 VALUES ( 1, 'aa', 2, 2 ), ( 2, 'bb', 2, 3 ), ( 3, 'cc', 2, 1 ), ( 4, 'dd', 2, 4 ), ( 5, 'ee', 2, 5 ), ( 6, 'ff', 3, 3 ), ( 7, 'gg', 3, 2 ), ( 8, 'hh', 3, 1 ), ( 9, 'ii', 4, 7 ), ( 10, 'jj', 4, 4 ), ( 11, 'kk', 4, 1 ), ( 12, 'll', 4, 3 ), ( 13, 'mm', 6, 1 ), ( 14, 'nn', 6, 2 ), ( 15, 'oo', 6, 3 ), ( 16, 'pp', 6, 4 ); WITH cte1 AS ( SELECT t1.ID , t1.Name , t1.RelID , t2.Data , ROW_NUMBER() OVER ( PARTITION BY t1.ID ORDER BY t2.Ordering ) AS rn FROM @t1 t1 JOIN @t2 t2 ON t1.RelID = t2.RelID ), cte2 AS ( SELECT ID , Name , RelID , Data , rn , STUFF(( SELECT ',' + Data FROM cte1 ci WHERE co.ID = ci.ID AND rn > 2 FOR XML PATH('') ), 1, 1, '') AS Col3 FROM cte1 co ) SELECT ID , Name , RelID , [1] AS Col1 , [2] AS Col2 , Col3 FROM cte2 PIVOT( MAX(data) FOR rn IN ( [1], [2] ) ) p

Sortie:

 ID Name RelID Col1 Col2 Col3 1 a 2 cc aa bb,dd,ee 2 b 3 hh gg ff 3 c 4 kk ll jj,ii 4 d 3 hh gg ff 5 e 6 mm nn oo,pp

Plan d'exécution de ma déclaration

entrez la description de l'image ici

Plan d'exécution de la déclaration acceptée:

entrez la description de l'image ici

Ce qui est mieux? 🙂