I have a problem and it is not resolved
I have a table with this structure. The two fields are string. Example
Articulo Año
aaa 2003
bbb 2003
aaa 2004
bbb 2004
ccc 2004
aaa 2005
ccc 2005
can a pivot be made to be like this?
2003 2004 2005
aaa aaa aaa
bbb bbb
ccc cccc
Thanks
The short answer is "yes, you can", but I imagine you would also like to know how to do it.
One problem to consider is that it is not simply a dynamic pivot that you need, but you also need that the results appear in order depending on the article to which the data corresponds. For this, you will have to use CROSS JOIN , in addition to ROW_NUMBER :
DECLARE @sql NVARCHAR(MAX) = N'', @cols NVARCHAR(MAX) = N'';
SELECT @cols += STUFF((SELECT ',' + QUOTENAME(Año)
FROM #TuTabla
GROUP BY Año
FOR XML PATH('')), 1, 1, '');
SET @sql = N'SELECT ' + @cols + '
FROM (SELECT z.Articulo, y.Año, ROW_NUMBER() OVER(PARTITION BY y.Año ORDER BY x.Articulo) RN
FROM ( SELECT DISTINCT articulo
FROM #TuTabla) x
CROSS JOIN (SELECT DISTINCT Año
FROM #TuTabla) y
LEFT JOIN #TuTabla z
ON x.Articulo = z.Articulo
AND y.Año = z.Año
) AS d
PIVOT (MIN([Articulo]) FOR [Año] IN (' + @cols + ')) AS p;';
EXEC sp_executesql @sql;
The result is:
╔══════╦══════╦══════╗
║ 2003 ║ 2004 ║ 2005 ║
╠══════╬══════╬══════╣
║ aaa ║ aaa ║ aaa ║
║ bbb ║ bbb ║ NULL ║
║ NULL ║ ccc ║ ccc ║
╚══════╩══════╩══════╝
UPDATE:
If you want the result as you explain in your comment, the code is much simpler:
DECLARE @sql NVARCHAR(MAX) = N'', @cols NVARCHAR(MAX) = N'';
SELECT @cols += STUFF((SELECT ',' + QUOTENAME(Año)
FROM #TuTabla
GROUP BY Año
FOR XML PATH('')), 1, 1, '');
SET @sql = N'SELECT ' + @cols + '
FROM (SELECT *, ROW_NUMBER() OVER(PARTITION BY Año ORDER BY Articulo) RN
FROM #TuTabla) AS d
PIVOT (MIN([Articulo]) FOR [Año] IN (' + @cols + ')) AS p;';
EXEC sp_executesql @sql;
The result in this case is:
╔══════╦══════╦══════╗
║ 2003 ║ 2004 ║ 2005 ║
╠══════╬══════╬══════╣
║ aaa ║ aaa ║ aaa ║
║ bbb ║ bbb ║ ccc ║
║ NULL ║ ccc ║ NULL ║
╚══════╩══════╩══════╝