Solved

Pivot or Crosstab

Posted on 2008-10-09
13
304 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
  • 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
 
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
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 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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

There are some very powerful Data 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 discu…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

707 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

11 Experts available now in Live!

Get 1:1 Help Now