SQL Server 2008 R2 – How to Display Data Using Pivot

sql serversql-server-2008-r2t-sql

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

Best Answer

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.

Related Solutions

Sql-server – Indexing – Uniqueidentifier Foreign Key or Intermediary mapping table

Ok, I am making a lot of assumptions (INT instead of VARCHAR(50) being one of them) with this answer, so feel free to correct me if needed. The problem with option B is that it introduces a new join to relate Users to Alerts without any real added benefit. If joining on the UserID, it is best to index the UserID, so you can utilize seeks for your joins.

For Option A, UserID will be the clustering key (index key for the clustered index) on the Users table. UserID will be a nonclustered index key on Alerts table. This will cost 16 bytes per Alert.

For Option B, UserID will be the clustering key on the Users table. UserId will probably be the clustering key in UserMap too, to make joining more efficient. UserKey (assuming this is an INT) would then be a nonclustered index key on the Alerts table. This will cost 4 bytes per Alert. And 20 bytes per UserMap.

Looking at the big picture, one relationship, for Option A, costs 16 bytes of storage, and involves 1 join operation. Whereas, one relationship, for Option B, costs 24 bytes of storage, and involves 2 join operations.

Furthermore, there are a possibility of 340,282,366,920,938,000,000,000,000,000,000,000,000 uniqueidentifiers and only 4,294,967,296 INTs. Implementing a uniqueidentifier to INT map for a this type of relationship could cause unexpected results when you start reusing INTs.

The only reason for creating this type map table, is if you plan on creating a Many to Many relationship between Users and Alerts.

Taking all of this into consideration, I would recommend Option A.

I hope this helps,

Matt

Sql-server – SQL Server 2008 R2 MERGE statement to replace single INSERT AND UPDATE statement combined

I haven't done any comparative testing of the two (yet) nor seen any articles on the topic. There is an Optimizing MERGE Statement Performance article on Technet but this doesn't include any comparisons with the update/insert syntax.

I can however suggest an improvement over your original syntax which eliminates the IF EXISTS lookup:

UPDATE 
    dbo.tblCustomer
SET 
    CustomerName = @CustomerName
WHERE
    CustomerID = @CustomerID;

IF (@@ROWCOUNT = 1)
BEGIN
    SELECT @New_ID = @CustomerID;
END
ELSE
BEGIN
    INSERT 
        dbo.tblCustomer
        (Taalnaam)
    VALUES
        (@CustomerName);

    SELECT @New_ID = SCOPE_IDENTITY();
END

You may also be interested in Mythbusting: Concurrent Update/Insert Solutions, which includes some examples of MERGE usage.

Best Answer

Related Solutions

Sql-server – Indexing – Uniqueidentifier Foreign Key or Intermediary mapping table

Sql-server – SQL Server 2008 R2 MERGE statement to replace single INSERT AND UPDATE statement combined

Related Question