3

Is a pivot the best way for me to display this data? I want to show id, uuid on the left then show each event1 across the top with a 1 if exists or a 0 if it does not. So look like this

id  uuid    cycle   run walk
1   mn12    1   1   0
2   cr23    0   0   1
3   bb11    1   0   0

This is the syntax I attempted, but it gives me an error of

Incorrect syntax near the keyword 'For'

What should I alter in order to have my data display as desired?

create table #robschneider
(
  ID int IDENTITY(1,1) PRIMARY KEY
  ,event1 varchar(100)
  ,uuid int
)
create table #bigponyhorses
(
  ID int IDENTITY(1,1) PRIMARY KEY
  ,uuid int
  ,empid varchar(10)
)
Insert Into #robschneider Values ('cycle', '1'), ('run', '2'), ('walk', '3')
Insert Into #bigponyhorses VALUES ('1','mn12'), ('2','mn12'), ('3', 'cr23'), ('1', 'bb11')

;WITH data AS 
(
  select *
  FROM
  (
      Select
      [rs].[ID],
      [bph].[uuid]
      [rs].[event1],
      CASE when [bph].[uuid] is not null then '1' else '0' end As [Exists]
      from #robschneider rs
      OUTER APPLY (SELECT * 
                   FROM #bigponyhorses  
                   WHERE [rs].[uuid] = [uuid]) [bph]
  ) src
  pivot 
  (
      [Exists]
      For [planname] IN ('cycle', 'run', 'walk')
  ) piv
)
SELECT 
[rs].[ID],
[rs].[event1],
'cycle', 'run', 'walk'
FROM data
1
  • Can you post the DDL for the [Intranet].[dbo].[ResourceDevelopementCompletedCategories] table please? You also have a missing comma after [bph].[uuid] Feb 17, 2016 at 15:17

1 Answer 1

2

I couldn't include [Intranet].[dbo].[ResourceDevelopementCompletedCategories] in my test query below but hopefully I have provided enough code for you to understand the syntax you require.

create table #robschneider
(
  ID int IDENTITY(1,1) PRIMARY KEY
  ,event1 varchar(100)
  ,uuid int
)

Insert Into #robschneider Values ('cycle', '1'), ('run', '2'), ('walk', '3')

SELECT  ID
        ,uuid
        ,COALESCE(cycle, 0) AS [Cycle]
        ,COALESCE(run, 0) AS [Run]
        ,COALESCE(Walk, 0) AS [Walk]
FROM
(
    Select
    [rs].[ID],
    [rs].[uuid],
    [rs].[event1],
    CASE when [rs].[uuid] is not null then '1' else '0' end As [Exists]
    from #robschneider rs
) src
pivot 
(
    MAX([Exists]) For [event1] IN ([cycle], [run], [walk])
) piv

UPDATE I have added the COALESCE statements to add the 0 values when event1 doesn't exist.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.