?
Solved

SQL pivot question

Posted on 2014-11-17
7
Medium Priority
?
240 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 51

Accepted Solution

by:
Vitor Montalvão earned 2000 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 51

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 2000 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 51

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 2000 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
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…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

762 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