Solved

SQL pivot question

Posted on 2014-11-17
7
228 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 45

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 45

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 45

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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

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.
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…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

708 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