Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Pivot or Crosstab

Posted on 2008-10-09
13
Medium Priority
?
331 Views
Last Modified: 2010-03-19
I have a table that looks like so:

MESSAGE_DATE                            VEHICLE                                 STATUS
=======================================================================
7/1/2008 4:10:39 AM                       2233-22                                WAITING
7/1/2008 7:18:24 AM                       321-437                                BOARD
7/1/2008 3:12:19 AM                       82611                                   BOARD
7/1/2008 9:12:29 AM                       989823                                 WAITING
7/1/2008 4:10:29 AM                       8221                                     BOARD

And I need to be able to do a pivot or something to make it look like so:

                                                      2233-22               321-437         82611        989823           8221
================================================================================
7/1/2008 4:10:39 AM                    WAITING
7/1/2008 7:18:24 AM                                                 BOARD
7/1/2008 3:12:19 AM
7/1/2008 9:12:29 AM
7/1/2008 4:10:29 AM
0
Comment
Question by:chrisryhal
[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
  • 7
  • 4
13 Comments
 
LVL 13

Expert Comment

by:sm394
ID: 22681215
Try that

SELECT *
FROM (
SELECT Message_Date, Vehicle, status
FROM table1 ) AS pvt
PIVOT ( MAX(status) FOR Vehicle IN
([2233-22],[321-437],[82611])
) AS ourPivot

0
 
LVL 2

Author Comment

by:chrisryhal
ID: 22681288
Awesome, but do I always have to hard code the vehicle?  
0
 
LVL 13

Expert Comment

by:sm394
ID: 22681386
yes in that case
but
No if you use dynamic sql
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 2

Author Comment

by:chrisryhal
ID: 22681410
Ok, I am confused on what you mean by dynamic SQL.  I will always be having different Vehicles coming and doing, and really want this to be a VIEW object that doesn't have to be changed all of the time.
0
 
LVL 13

Expert Comment

by:sm394
ID: 22681432
With Dynamic SQL
DECLARE @VehicleNames NVARCHAR(MAX) 
	SET @VehicleNames='[2233-22],[321-437],[82611]'
	DECLARE @SQL NVARCHAR(MAX) 
SET @SQL=	'SELECT *
FROM (
SELECT Message_Date, Vehicle, status
FROM table1 ) AS pvt
PIVOT ( MAX(status) FOR Vehicle IN
('+@VehicleNames+')) AS PVT'
	
EXEC sp_executesql @SQL

Open in new window

0
 
LVL 13

Expert Comment

by:sm394
ID: 22681461
with above dyamic sql you can pass vehicle names as parameter
@VehicleNames='[2233-22],[321-437],[82611]'
0
 
LVL 2

Author Comment

by:chrisryhal
ID: 22681505
Yeah but I still see the need to have to maually enter in the vehicle numbers either way.  can I just do a loop or select distinct from VEHICLE or sometihng?  Sorry I'm being such a pain.
0
 
LVL 13

Expert Comment

by:sm394
ID: 22681567
something like this

SELECT  @VehicleNames= COALESCE(@VehicleNames+',','')+'['+ Vehicle+']'
      FROM Table1
0
 
LVL 2

Author Comment

by:chrisryhal
ID: 22681654
Anyway you would put it into perspective with what you have already showed me?

I REALLY greatly appreciate all your help with this task.  You have been most helpful.
0
 
LVL 13

Accepted Solution

by:
sm394 earned 2000 total points
ID: 22681813
u welcome

DECLARE @VehicleNames NVARCHAR(MAX)
      SELECT  @VehicleNames= COALESCE(@VehicleNames+',','')+'['+ Vehicle+']'
      FROM Table1
      
      DECLARE @SQL NVARCHAR(MAX)
      SET @SQL=
      'SELECT *
FROM (
SELECT Message_Date, Vehicle, status
FROM table1 ) AS pvt
PIVOT ( MAX(status) FOR Vehicle IN
('+@VehicleNames+')) AS PVT'
      
      EXEC sp_executesql @SQL
0
 
LVL 13

Expert Comment

by:sm394
ID: 22681862
if you have duplicate values in vehicle fileld use query below
DECLARE @VehicleNames NVARCHAR(MAX) 
	select distinct vehicle into #tmp from table1
	SELECT  @VehicleNames= COALESCE(@VehicleNames+',','')+'['+ Vehicle+']'
	FROM #tmp;
	
	DECLARE @SQL NVARCHAR(MAX) 
	SET @SQL=
	'SELECT *
FROM (
SELECT Message_Date, Vehicle, status
FROM table1 ) AS pvt
PIVOT ( MAX(status) FOR Vehicle IN
('+@VehicleNames+')) AS PVT'
	
	EXEC sp_executesql @SQL

Open in new window

0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

There are some very powerful Dynamic Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a di…
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

715 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