Risultato Query in orizzontale

USE tempdb

-- prima tabella
CREATE TABLE T1
(
  Id int NOT NULL PRIMARY KEY CLUSTERED
)
GO

-- popolo con gli id
INSERT T1 
SELECT 1
INSERT T1 
SELECT 2
INSERT T1 
SELECT 3
INSERT T1 
SELECT 4
GO

-- seconda tabella
CREATE TABLE T2
(
  Id int NOT NULL,
  Lettera char(1) NOT NULL,
  CONSTRAINT PK_T2 PRIMARY KEY CLUSTERED
  (
      Id
    , Lettera
  )
)
GO

-- popolo con le lettere
INSERT T2 
SELECT 1,'A'
INSERT T2 
SELECT 1,'B'
INSERT T2 
SELECT 1,'C'
INSERT T2 
SELECT 2,'C'
INSERT T2 
SELECT 2,'F'
INSERT T2 
SELECT 3,'A'
INSERT T2 
SELECT 4,'B'
INSERT T2 
SELECT 4,'C'
INSERT T2 
SELECT 4,'A'
INSERT T2 
SELECT 4,'D'
GO

SELECT 
    T1.Id
  , T2.Lettera
FROM T1
JOIN T2 ON T1.Id = T2.Id
GO

CREATE FUNCTION dbo.ufn_Trasponi
(
  @Id int
)
RETURNS  varchar(8000) AS
BEGIN

  -- riga di ritorno  
  DECLARE @retRiga varchar(8000)
  SET @retRiga = ''
  
  -- concateno alla riga la lettera per ogni id passato
  SELECT @retRiga = @retRiga + ' ' + Lettera
  FROM  T1
  JOIN  T2 ON T1.Id = T2.Id
  WHERE   T1.Id = @Id

  RETURN @retRiga
END
GO 

SELECT 
  DISTINCT 
      T1.Id
    , dbo.ufn_Trasponi(T1.Id) 
FROM T1
JOIN T2 ON T1.Id = T2.Id
GO

DROP TABLE T1
DROP TABLE T2
DROP FUNCTION dbo.ufn_Trasponi
GO

Id
----------- ----------
1            A B C
2            C F
3            A
4            A B C D

(4 row(s) affected)

cod let1 let2 let3 let4 let5 ...
--- ---- ---- ---- ---- ----
1   A    B    C
2   C    F
3   A
4   B    C    A    D 

USE tempdb;

CREATE TABLE dbo.T1(
Id int NOT NULL PRIMARY KEY
);

CREATE TABLE dbo.T2(
Id int NOT NULL IDENTITY PRIMARY KEY,
T1_Id int NOT NULL,
Lettera char(1) NOT NULL,
CONSTRAINT FK_T2_T1 FOREIGN KEY(T1_Id)
REFERENCES dbo.T1(Id)
);

INSERT dbo.T1 VALUES(1);
INSERT dbo.T1 VALUES(2);
INSERT dbo.T1 VALUES(3);
INSERT dbo.T1 VALUES(4);

INSERT dbo.T2 VALUES(1, 'A');
INSERT dbo.T2 VALUES(1, 'B');
INSERT dbo.T2 VALUES(1, 'C');
INSERT dbo.T2 VALUES(2, 'C');
INSERT dbo.T2 VALUES(2, 'F');
INSERT dbo.T2 VALUES(3, 'A');
INSERT dbo.T2 VALUES(4, 'B');
INSERT dbo.T2 VALUES(4, 'C');
INSERT dbo.T2 VALUES(4, 'A');
INSERT dbo.T2 VALUES(4, 'D');
GO

/* Query Crosstab */ 
CREATE VIEW dbo.vwCrosstab 
AS 
SELECT 
      T1_Id
    , MAX(CASE Riga
              WHEN 1 THEN Lettera
              ELSE ''
          END
      ) AS Let1
    , MAX(CASE Riga
              WHEN 2 THEN Lettera
              ELSE ''
          END
      ) AS Let2
    , MAX(CASE Riga
              WHEN 3 THEN Lettera
              ELSE ''
          END
      ) AS Let3
    , MAX(CASE Riga
              WHEN 4 THEN Lettera
              ELSE ''
          END
      ) AS Let4
    , MAX(CASE Riga
              WHEN 5 THEN Lettera
              ELSE ''
          END
      ) AS Let5
    , MAX(CASE Riga
              WHEN 6 THEN Lettera
              ELSE ''
          END
      ) AS Let6
    , MAX(CASE Riga
              WHEN 7 THEN Lettera
              ELSE ''
          END
      ) AS Let7
    , MAX(CASE Riga
              WHEN 8 THEN Lettera
              ELSE ''
          END
      ) AS Let8
    , MAX(CASE Riga
              WHEN 9 THEN Lettera
              ELSE ''
          END
      ) AS Let9
    , MAX(CASE Riga
              WHEN 10 THEN Lettera
              ELSE ''
          END
      ) AS Let10

FROM (
    SELECT T1_Id, Lettera, (
        SELECT COUNT(*)
        FROM dbo.T2
        WHERE Q1.Id >= Id
        AND Q1.T1_Id = T1_Id
    ) AS Riga
    FROM dbo.T2 AS Q1
) AS Q
GROUP BY T1_Id
GO

/* Query finale */
SELECT V.*
FROM dbo.T1
JOIN dbo.vwCrosstab AS V
ON T1.Id = V.T1_Id;

/* Output:

T1_Id       Let1 Let2 Let3 Let4 Let5 Let6 Let7 Let8 Let9 Let10
----------- ---- ---- ---- ---- ---- ---- ---- ---- ---- -----
1           A    B    C                                  
2           C    F                                       
3           A                                            
4           B    C    A    D                             

(4 row(s) affected)

*/

DROP VIEW dbo.vwCrosstab;
DROP TABLE dbo.T2, dbo.T1;