T-SQL example: dynamically generate columns by table

DECLARE @Seria INT
SET @Seria = 15

DECLARE @Kpcount INT
SELECT @Kpcount = Pairs * Sections FROM S_SeriaTPS WHERE ID = @Seria

DECLARE @Cnt INT
SET @Cnt=1

-- Colums list string
DECLARE @sf_1 VARCHAR(MAX)
SET @sf_1 = ''

-- Join section string
DECLARE @sj_1 VARCHAR(MAX)
SET @sj_1 = ''

DECLARE @ChkVal INT
SET @ChkVal = 0

WHILE (@Cnt <= @Kpcount)
BEGIN
SET @sf_1 = @sf_1 +
', t0' + RTRIM(CAST(@Cnt AS CHAR(2))) + '.RepDate AS [Date ' + RTRIM(CAST(@Cnt AS CHAR(2))) + '], ' +
't0' + RTRIM(CAST(@Cnt AS CHAR(2))) + '.DiamAv AS [Diameter avg ' + RTRIM(CAST(@Cnt AS CHAR(2))) + '] '

-- Generate join section
SET @sj_1 = @sj_1 +
'LEFT JOIN (
SELECT
tk.IDTps,
cm.RepDate,
tk.Position,
DiamAv =
ABS(
COALESCE((bm.DiamL - (bm.TolshBandL - cm.TolshBandL) * 2), bm.DiamL) +
COALESCE((bm.DiamR - (bm.TolshBandR - cm.TolshBandR) * 2), bm.DiamR)) / 2
FROM TpsKp tk
LEFT JOIN BasicMeasurements bm ON bm.IDKp=tk.IDKp
LEFT JOIN Measurements cm ON
cm.ID=(
SELECT TOP 1 cm1.ID
FROM Measurements cm1
WHERE cm1.IDKp=tk.IDKp
ORDER BY cm1.RepDate DESC)
WHERE tk.Position=' + RTRIM(CAST(@Cnt AS CHAR(2))) +
') t0' + RTRIM(CAST(@Cnt AS CHAR(2))) + ' ON t0' + RTRIM(CAST(@Cnt AS CHAR(2))) + '.IDTps=t.ID '

-- Column counter increment
SET @Cnt = @Cnt + 1
CONTINUE
END

DECLARE @sql VARCHAR(MAX)
SET @sql =
'SELECT RTrim(t.Nomer) AS [Name] {0} FROM Tps t {1} WHERE t.IDSeria=' +
RTRIM(CAST(@Seria AS CHAR(3))) + ' ' +
'ORDER BY Nomer'

IF @sf_1 = ''
BEGIN
SET
@sf_1 = ', ''no data'' AS [Info]'
END

SET @sql = REPLACE(@sql, '{0}', @sf_1)
SET @sql = REPLACE(@sql, '{1}', @sj_1)

EXEC (@sql)

-----------------------
Result seem's like this
Name Date 1 Diameter avg 1 Date 2 Diameter avg 2
316 2007-12-28 13:11:23.000 1254 2007-12-28 13:12:16.000 1255
319 2008-01-19 14:55:00.000 1250 2008-01-19 14:56:30.000 1255
321 2008-02-15 13:15:21.000 1212 2008-02-15 13:15:37.000 1213