Pivot or Crosstab

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
LVL 2
chrisryhalAsked:
Who is Participating?
 
sm394Connect With a Mentor Commented:
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
 
sm394Commented:
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
 
chrisryhalAuthor Commented:
Awesome, but do I always have to hard code the vehicle?  
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
sm394Commented:
yes in that case
but
No if you use dynamic sql
0
 
chrisryhalAuthor Commented:
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
 
sm394Commented:
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
 
sm394Commented:
with above dyamic sql you can pass vehicle names as parameter
@VehicleNames='[2233-22],[321-437],[82611]'
0
 
chrisryhalAuthor Commented:
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
 
sm394Commented:
something like this

SELECT  @VehicleNames= COALESCE(@VehicleNames+',','')+'['+ Vehicle+']'
      FROM Table1
0
 
chrisryhalAuthor Commented:
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
 
sm394Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.