Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 335
  • Last Modified:

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
0
chrisryhal
Asked:
chrisryhal
  • 7
  • 4
1 Solution
 
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
 
sm394Commented:
yes in that case
but
No if you use dynamic sql
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

  • 7
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now