Advertisement
Guest User

Untitled

a guest
Feb 10th, 2016
54
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 2.03 KB | None | 0 0
  1. ---------------------------------
  2. | IdTable | NRow | NCol | Value |
  3. ---------------------------------
  4. | 001 | 1 | 1 | Наим. |
  5. ---------------------------------
  6. | 001 | 1 | 2 | № стр.|
  7. ---------------------------------
  8. | 001 | 1 | 3 | Всего |
  9. ---------------------------------
  10. | 001 | 2 | 1 | 1 |
  11. ---------------------------------
  12. | 001 | 2 | 2 | 2 |
  13. ---------------------------------
  14. | 001 | 2 | 3 | 3 |
  15. ---------------------------------
  16. | 001 | 3 | 1 | прод1 |
  17. ---------------------------------
  18. | 001 | 3 | 2 | 20 |
  19. ---------------------------------
  20. | 001 | 3 | 3 | 256 |
  21. ---------------------------------
  22. | 002 | ... | ... | ... |
  23. ---------------------------------
  24. | 003 | ... | ... | ... |
  25. ---------------------------------
  26. | 004 | ... | ... | ... |
  27. ---------------------------------
  28.  
  29. IF OBJECT_ID('tempdb..#Table')IS NOT NULL DROP TABLE #Table
  30. CREATE TABLE #Table(
  31. IdTable INT,
  32. NRow INT,
  33. NCol INT,
  34. Value NVARCHAR(4000)
  35. )
  36. DECLARE @IdTable INT, @NRow INT, @NCol INT
  37. INSERT #Table VALUES
  38. (1,1,1,'Наим'),
  39. (1,1,2,'№'),
  40. (1,1,3,'Всего'),
  41. (1,2,1,'1'),
  42. (1,2,2,'2'),
  43. (1,2,3,'3'),
  44. (1,3,1,'прод1'),
  45. (1,3,2,'20'),
  46. (1,3,3,'256'),
  47. (2,1,1,'АА'),
  48. (2,1,2,'ББ'),
  49. (2,2,1,'11'),
  50. (2,2,2,'22')
  51.  
  52. SELECT @IdTable = 1, @NRow = 3, @NCol = 3
  53.  
  54. DECLARE
  55. @SQL NVARCHAR(4000)
  56. ;WITH CTE AS(
  57. SELECT 1 N
  58. UNION ALL
  59. SELECT N+1
  60. FROM CTE
  61. WHERE N<@NCol
  62. )
  63. SELECT @SQL = STUFF((SELECT CONCAT(',[',N,']') FROM CTE ORDER BY N FOR XML PATH('')),1,1,'')
  64. OPTION(MAXRECURSION 256)
  65.  
  66. SET @SQL =
  67. 'SELECT ' + @SQL + '
  68. FROM(
  69. SELECT NRow, NCol, Value
  70. FROM #Table
  71. WHERE IdTable = ' + CAST(@IdTable AS VARCHAR(11)) +
  72. ')T PIVOT(
  73. MAX(Value) FOR NCol IN(' + @SQL + ')
  74. )P'
  75.  
  76. SELECT @SQL
  77.  
  78. EXEC(@SQL)
  79.  
  80. SELECT [1],[2],[3]
  81. FROM(
  82. SELECT NRow, NCol, Value
  83. FROM #Table
  84. WHERE IdTable = 1)T PIVOT(
  85. MAX(Value) FOR NCol IN([1],[2],[3])
  86. )P
  87.  
  88. Наим № Всего
  89. 1 2 3
  90. прод1 20 256
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement