Skip to Content
May 25, 2020 at 07:32 PM

Rows to Columns issue?

57 Views Last edit May 28, 2020 at 06:36 AM 2 rev Guys,

Fairly new to SQL (Due to do a beginners training course the start of next month) I have the below but it seems a really inefficient way to do it and i believe i am doing it very wrong. I ahve looked at Pivot tables but cannot seem to wrap my head around it.

Where i have UF13 and UF14 i would have to do this for around 24 variations. Essentially i am trying to take specific row data and give it it's own column as for my purpose i need it all in one row. In the other table [design_user_fields] this is broken into 24 rows per file [designname]

<code>FROM [ArtiosCADdb].[programmer].[design]
inner join [ArtiosCADdb].[programmer].[resource_table] on [ArtiosCADdb].[programmer].[resource_table].[resourceid] = [ArtiosCADdb].[programmer].[design].[designResource]
  left outer join [ArtiosCADdb].[programmer].[project_printitem] on [ArtiosCADdb].[programmer].[project_printitem].[designname] = [ArtiosCADdb].[programmer].[design].[designName]
  left outer join [ArtiosCADdb].[programmer].[design_user_fields] as UF13 on UF13.[designName] = [ArtiosCADdb].[programmer].[design].[designName] and UF13.fieldid = '13'
  left outer join [ArtiosCADdb].[programmer].[design_user_fields] as UF14 on UF14.[designName] = [ArtiosCADdb].[programmer].[design].[designName] and UF14.fieldid = '14'
  where [design].designRevisionDate >= Convert(datetime, '2018-01-01' ) and [design]