Solved

SQL pivot question

Posted on 2014-11-17
7
231 Views
Last Modified: 2014-11-18
Hi SQL server experts,

I've got this some what older SQL server running (version 9.00.5057.00) and I try to make Pivot query work.

I've got this query that is successful on a access database. But now I'd like to make it work in MS SQL.

TRANSFORM Min("X") AS Expr1 SELECT membership.groep FROM membership GROUP BY membership.groep PIVOT membership.username;

Open in new window



But it does not work on my old sql server.
Is this because my server it to old and my SQL server does not support pivoting?

Or is something wrong with my syntax?

The result should show a table with horizontal usernames, vertical groupnames end where they match an X marks the spot.
Below the desired result:
results
Kind regards,
0
Comment
Question by:Steynsk
  • 3
  • 3
7 Comments
 
LVL 7

Expert Comment

by:slubek
ID: 40448356
There is no TRANSFORM command in Technet article about using PIVOT. I think that command is only in MS Access SQL dialect.
Unfortunately, I don't have an access to my MS SQL Server just now, so I cannot tell you the proper form of SQL command you need, but read about converting PIVOT query from Access to SQL Server.
0
 
LVL 46

Accepted Solution

by:
Vitor Montalvão earned 500 total points
ID: 40449221
You can't use the same MS Access's syntax in MS SQL Server.
PIVOT exists in SQL Server but only works together with an aggregate function and you need to explicitly tell to the engine which values are part of the PIVOT.
In your example I think the SQL Server code should be something like:
SELECT *
FROM (
           SELECT groep 
           FROM membership
) t
PIVOT 
(MIN(ColumnNameHere) FOR username IN (Billy, Judy, John)) p

Open in new window

0
 
LVL 1

Author Comment

by:Steynsk
ID: 40449626
Hi Slubek en Vitor,

Thanks for your responses.

I can't get to work for me.

Maybe I should give you the table layout:

CREATE TABLE membership(
	[memberID] [int] NOT NULL,
	[username] [nvarchar](50) NULL,
	[groupname] [nvarchar](50) NULL
)

insert into membership (memberID,username,groupname) values(28,'Bill','Domain users')
insert into membership (memberID,username,groupname) values(30,'Bill','Application B')
insert into membership (memberID,username,groupname) values(33,'Judy','Domain users')
insert into membership (memberID,username,groupname) values(34,'Judy','Application A')
insert into membership (memberID,username,groupname) values(36,'John','Domain users')
insert into membership (memberID,username,groupname) values(37,'John','Application B')
insert into membership (memberID,username,groupname) values(38,'John','Printer 45')

Open in new window


I hope that helps.
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 46

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 500 total points
ID: 40449639
Try this one:
SELECT *
FROM (
           SELECT groupname, memberID, username
           FROM membership
) t
PIVOT 
(MIN(memberID) FOR username IN (Bill, Judy, John)) p

Open in new window

0
 
LVL 1

Author Comment

by:Steynsk
ID: 40449653
It more or less works (see the sqlfiddle below):

http://sqlfiddle.com/#!3/2d0f6/1

But it is limited to called users in the query. And It should show all users (including Abe) without the need to include him in the query.
0
 
LVL 46

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 500 total points
ID: 40449664
That's the problem with PIVOT in SQL Server. Isn't dynamic. You really need to specify which records you want to use for PIVOT.
But someone already had the same issue and solve it like this.
0
 
LVL 1

Author Closing Comment

by:Steynsk
ID: 40449671
Ok I understand.  Thanks for the solution.
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

914 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now