Solved

Pivot or Crosstab

Posted on 2008-10-09
13
318 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: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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 500 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

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!

Question has a verified solution.

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

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

710 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