API tools faq
paste
Guest User

Untitled

a guest
Jun 25th, 2018
79
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 8.26 KB | None | 0 0
raw download clone embed print report
  1. CROSS APPLY
  2.  
  3. CROSS APPLY
  4.  
  5. SELECT distinct e.[Event Name],
  6. e.[Resource Type],
  7. LEFT(r.ResourceName , LEN(r.ResourceName)-1) ResourceName
  8. FROM yourtable e
  9. CROSS APPLY
  10. (
  11. SELECT r.[Resource Name] + ', '
  12. FROM yourtable r
  13. where e.[Event Name] = r.[Event Name]
  14. and e.[Resource Type] = r.[Resource Type]
  15. FOR XML PATH('')
  16. ) r (ResourceName)
  17.  
  18. | EVENT NAME | RESOURCE TYPE | RESOURCENAME |
  19. ------------------------------------------------------------------------
  20. | Event 1 | Resource Type 1 | Resource 1, Resource 2 |
  21. | Event 1 | Resource Type 2 | Resource 3, Resource 4 |
  22. | Event 1 | Resource Type 3 | Resource 5, Resource 6, Resource 7 |
  23. | Event 1 | Resource Type 4 | Resource 8 |
  24. | Event 2 | Resource Type 2 | Resource 3 |
  25. | Event 2 | Resource Type 3 | Resource 11, Resource 12, Resource 13 |
  26. | Event 2 | Resource Type 4 | Resource 14 |
  27. | Event 2 | Resource Type 5 | Resource 1, Resource 9, Resource 16 |
  28.  
  29. SELECT [Event Name],
  30. [Resource Type 1], [Resource Type 2],
  31. [Resource Type 3], [Resource Type 4],
  32. [Resource Type 5]
  33. FROM
  34. (
  35. SELECT distinct e.[Event Name],
  36. e.[Resource Type],
  37. LEFT(r.ResourceName , LEN(r.ResourceName)-1) ResourceName
  38. FROM yourtable e
  39. CROSS APPLY
  40. (
  41. SELECT r.[Resource Name] + ', '
  42. FROM yourtable r
  43. where e.[Event Name] = r.[Event Name]
  44. and e.[Resource Type] = r.[Resource Type]
  45. FOR XML PATH('')
  46. ) r (ResourceName)
  47. ) src
  48. pivot
  49. (
  50. max(ResourceName)
  51. for [Resource Type] in ([Resource Type 1], [Resource Type 2],
  52. [Resource Type 3], [Resource Type 4],
  53. [Resource Type 5])
  54. ) piv
  55.  
  56. | EVENT NAME | RESOURCE TYPE 1 | RESOURCE TYPE 2 | RESOURCE TYPE 3 | RESOURCE TYPE 4 | RESOURCE TYPE 5 |
  57. ----------------------------------------------------------------------------------------------------------------------------------------------------------------
  58. | Event 1 | Resource 1, Resource 2 | Resource 3, Resource 4 | Resource 5, Resource 6, Resource 7 | Resource 8 | (null) |
  59. | Event 2 | (null) | Resource 3 | Resource 11, Resource 12, Resource 13 | Resource 14 | Resource 1, Resource 9, Resource 16 |
  60.  
  61. IF OBJECT_ID('tempdb..#test') IS NOT NULL
  62. DROP TABLE #test
  63.  
  64. GO
  65.  
  66. CREATE TABLE #test
  67. (
  68. eventName VARCHAR(30),
  69. resourceType VARCHAR(30),
  70. resourceName VARCHAR(30)
  71. );
  72.  
  73. INSERT INTO #test
  74. VALUES ('Event 1','Resource Type 1','Resource 1'),
  75. ('Event 1','Resource Type 1','Resource 2'),
  76. ('Event 1','Resource Type 2','Resource 3'),
  77. ('Event 1','Resource Type 2','Resource 4'),
  78. ('Event 1','Resource Type 3','Resource 5'),
  79. ('Event 1','Resource Type 3','Resource 6'),
  80. ('Event 1','Resource Type 3','Resource 7'),
  81. ('Event 1','Resource Type 4','Resource 8'),
  82. ('Event 2','Resource Type 5','Resource 1'),
  83. ('Event 2','Resource Type 2','Resource 3'),
  84. ('Event 2','Resource Type 3','Resource 11'),
  85. ('Event 2','Resource Type 3','Resource 12'),
  86. ('Event 2','Resource Type 3','Resource 13'),
  87. ('Event 2','Resource Type 4','Resource 14'),
  88. ('Event 2','Resource Type 5','Resource 9'),
  89. ('Event 2','Resource Type 5','Resource 16');
  90.  
  91. DECLARE @resourceTypes VARCHAR(max);
  92.  
  93. SELECT @resourceTypes = stuff((SELECT DISTINCT ',[' + resourceType + ']'
  94. FROM #test
  95. FOR xml path('')), 1, 1, '');
  96. DECLARE @query NVARCHAR(max);
  97.  
  98. SET @query = 'SELECT *
  99. FROM (SELECT eventName,
  100. resourceType,
  101. stuff((SELECT '','' + resourceName + ''''
  102. FROM #test b
  103. WHERE a.eventName = b.eventName
  104. AND a.resourceType = b.resourceType
  105. FOR xml path('''')), 1, 1, '''') resourceName
  106. FROM #test a
  107. GROUP BY eventName,
  108. resourceType) AS data PIVOT (max(resourceName) FOR resourceType IN (' + @resourceTypes + ')) AS pvt';
  109.  
  110. EXEC(@query);
  111.  
  112. DROP TABLE #test;
  113.  
  114. =Join( LookupSet( Fields!EVENT_NAME.Value + Fields!RESOURCE_TYPE.Value,
  115. Fields!EVENT_NAME.Value + Fields!RESOURCE_TYPE.Value,
  116. Fields!RESOURCE_NAME.Value, "DataSet1"), ", ")
  117.  
  118. SET NOCOUNT ON
  119. GO
  120.  
  121. DECLARE @SourceTable TABLE
  122. (
  123. EventName NVARCHAR(10)
  124. ,ResourceType NVARCHAR(20)
  125. ,ResourceName NVARCHAR(20)
  126. )
  127.  
  128. INSERT INTO @SourceTable(EventName,ResourceType,ResourceName)
  129. VALUES ('Event 1','Resource Type 1','Resource 1')
  130. ,('Event 1','Resource Type 1','Resource 2')
  131. ,('Event 1','Resource Type 2','Resource 3')
  132. ,('Event 1','Resource Type 2','Resource 4')
  133. ,('Event 1','Resource Type 3','Resource 5')
  134. ,('Event 1','Resource Type 3','Resource 6')
  135. ,('Event 1','Resource Type 3','Resource 7')
  136. ,('Event 1','Resource Type 4','Resource 8')
  137. ,('Event 2','Resource Type 5','Resource 1')
  138. ,('Event 2','Resource Type 2','Resource 3')
  139. ,('Event 2','Resource Type 3','Resource 11')
  140. ,('Event 2','Resource Type 3','Resource 12')
  141. ,('Event 2','Resource Type 3','Resource 13')
  142. ,('Event 2','Resource Type 4','Resource 14')
  143. ,('Event 2','Resource Type 5','Resource 9')
  144. ,('Event 2','Resource Type 5','Resource 16')
  145.  
  146. ;WITH SourceTable AS
  147. (
  148. SELECT DISTINCT ST1.EventName
  149. ,ST1.ResourceType
  150. ,(SELECT SUBSTRING((SELECT ',' +ResourceName
  151. FROM @SourceTable AS ST2
  152. WHERE ST1.EventName=ST2.EventName AND ST1.ResourceType=ST2.ResourceType
  153. FOR XML PATH('')),2,200) AS CSV) AS ResourceName
  154. FROM @SourceTable AS ST1
  155. )
  156. SELECT EventName
  157. ,[Resource Type 1]
  158. ,[Resource Type 2]
  159. ,[Resource Type 3]
  160. ,[Resource Type 4]
  161. ,[Resource Type 5]
  162. FROM
  163. (
  164. SELECT EventName
  165. ,ResourceType
  166. ,ResourceName
  167. FROM SourceTable
  168. ) PivotSource
  169. PIVOT
  170. (
  171. MAX(ResourceName) FOR ResourceType IN ([Resource Type 1],[Resource Type 2],[Resource Type 3],[Resource Type 4],[Resource Type 5])
  172. ) PivotTable
  173.  
  174. SET NOCOUNT OFF
  175. GO
  176.  
  177. id displayname Group
  178. -------------------------------------
  179. d-5454-s34 name Frans
  180. d-5454-s34 sd xyh
  181. d-5454-s34 description Group zen
  182. d-5454-s34 member xxxx
  183. d-5454-s34 member yyyy
  184. d-5454-s34 member zzzzz
  185. d-5454-s34 member uuuuu
  186. d-5454-s45 name He-man
  187. d-5454-s45 sd ygh
  188. d-5454-s45 description Group Comics
  189. d-5454-s45 member eeee
  190. d-5454-s45 member ffffff
  191. e-3434-t45 name Calvin
  192. e-3434-t45 sd trdg
  193.  
  194. id name sd description member
  195. ---------------------------------------------------------------------------
  196.  
  197. d-5454-s34 Frans xyh Group zen xxxx; yyyy; zzzzz; uuuuu
  198.  
  199. d-5454-s45 He-man ygh Group Comics eeee; ffffff
  200.  
  201. e-3434-t45 Calvin trdg NULL NULL
  202.  
  203. SELECT distinct a.id, a.displayname, LEFT(r.[Description] , LEN(r.[Description])-1) [Description]
  204. FROM [tdt_AD_Teste] a
  205. CROSS APPLY
  206. (
  207. SELECT r.[description] + '; '
  208. FROM [tdt_AD_Teste] r
  209. where a.[id] = r.[id]
  210. and a.[displayname] = r.[displayname]
  211. FOR XML PATH('')
  212. ) r ([Description])
  213.  
  214. id name sd description member
  215. ---------------------------------------------------------------------------
  216.  
  217. d-5454-s34 Frans xyh Group zen xxxx; xxxx; xxxx; xxxx (and it keeps to repeat)
  218.  
  219. d-5454-s45 He-man ygh Group Comics eeee; eeee; eeee; eeee; (and it keeps to repeat)
  220.  
  221. e-3434-t45 Calvin trdg ;;;;;;;;;;; ;;;;;;;;;;;;;;;;; (and it keeps to repeat)
Add Comment
Please, Sign In to add comment
Public Pastes
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!