Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- ---------------------------------
- | IdTable | NRow | NCol | Value |
- ---------------------------------
- | 001 | 1 | 1 | Наим. |
- ---------------------------------
- | 001 | 1 | 2 | № стр.|
- ---------------------------------
- | 001 | 1 | 3 | Всего |
- ---------------------------------
- | 001 | 2 | 1 | 1 |
- ---------------------------------
- | 001 | 2 | 2 | 2 |
- ---------------------------------
- | 001 | 2 | 3 | 3 |
- ---------------------------------
- | 001 | 3 | 1 | прод1 |
- ---------------------------------
- | 001 | 3 | 2 | 20 |
- ---------------------------------
- | 001 | 3 | 3 | 256 |
- ---------------------------------
- | 002 | ... | ... | ... |
- ---------------------------------
- | 003 | ... | ... | ... |
- ---------------------------------
- | 004 | ... | ... | ... |
- ---------------------------------
- IF OBJECT_ID('tempdb..#Table')IS NOT NULL DROP TABLE #Table
- CREATE TABLE #Table(
- IdTable INT,
- NRow INT,
- NCol INT,
- Value NVARCHAR(4000)
- )
- DECLARE @IdTable INT, @NRow INT, @NCol INT
- INSERT #Table VALUES
- (1,1,1,'Наим'),
- (1,1,2,'№'),
- (1,1,3,'Всего'),
- (1,2,1,'1'),
- (1,2,2,'2'),
- (1,2,3,'3'),
- (1,3,1,'прод1'),
- (1,3,2,'20'),
- (1,3,3,'256'),
- (2,1,1,'АА'),
- (2,1,2,'ББ'),
- (2,2,1,'11'),
- (2,2,2,'22')
- SELECT @IdTable = 1, @NRow = 3, @NCol = 3
- DECLARE
- @SQL NVARCHAR(4000)
- ;WITH CTE AS(
- SELECT 1 N
- UNION ALL
- SELECT N+1
- FROM CTE
- WHERE N<@NCol
- )
- SELECT @SQL = STUFF((SELECT CONCAT(',[',N,']') FROM CTE ORDER BY N FOR XML PATH('')),1,1,'')
- OPTION(MAXRECURSION 256)
- SET @SQL =
- 'SELECT ' + @SQL + '
- FROM(
- SELECT NRow, NCol, Value
- FROM #Table
- WHERE IdTable = ' + CAST(@IdTable AS VARCHAR(11)) +
- ')T PIVOT(
- MAX(Value) FOR NCol IN(' + @SQL + ')
- )P'
- SELECT @SQL
- EXEC(@SQL)
- SELECT [1],[2],[3]
- FROM(
- SELECT NRow, NCol, Value
- FROM #Table
- WHERE IdTable = 1)T PIVOT(
- MAX(Value) FOR NCol IN([1],[2],[3])
- )P
- Наим № Всего
- 1 2 3
- прод1 20 256
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement