Solved

SQL pivot question

Posted on 2014-11-17
7
234 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 47

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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 47

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 47

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Access Open Report with SQL Parameter 11 30
Tsql query 6 22
optimize stored procedure 6 26
Need help constructing a conditional update query 16 43
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Viewers will learn how the fundamental information of how to create a table.

773 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