[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL Complex Select

Posted on 2012-09-17
7
Medium Priority
?
421 Views
Last Modified: 2012-09-20
I have the following data where there will be multiple instances of each ID and many ID's
(123, 124, 125, etc...)
There are of course many other repeating columns like ID

ID     Doc               ExpirationDate
123  CoreComp     2012-09-15 00:00:00.000
123  Derta             2012-10-01 00:00:00.000
123  Sertif             2012-09-10 00:00:00.000
124  CoreComp     2012-06-22 00:00:00.000
124  Sertif             2012-09-10 00:00:00.000


I want to return this
ID     CoreComp
123   2012-09-15 00:00:00.000
124    2012-06-22 00:00:00.000
0
Comment
Question by:lrbrister
7 Comments
 
LVL 8

Expert Comment

by:Crashman
ID: 38406617
Right now I don't have much time, but this can works, latter , maybe , I try create the dynamic query

DECLARE @tbl TABLE
    (
      ID INT ,
      Doc VARCHAR(50) ,
      ExpirationDate DATETIME
    )

INSERT  INTO @tbl
        ( ID ,
          Doc ,
          ExpirationDate
        )
VALUES  ( 123 ,
          'CoreComp' ,
          '2012-09-15 00:00:00.000'
        )
INSERT  INTO @tbl
        ( ID ,
          Doc ,
          ExpirationDate
        )
VALUES  ( 123 ,
          'Derta' ,
          '2012-10-01 00:00:00.000'
        )
INSERT  INTO @tbl
        ( ID ,
          Doc ,
          ExpirationDate
        )
VALUES  ( 123 ,
          'Sertif' ,
          '2012-09-10 00:00:00.000'
        )
INSERT  INTO @tbl
        ( ID ,
          Doc ,
          ExpirationDate
        )
VALUES  ( 124 ,
          'CoreComp' ,
          '2012-06-22 00:00:00.000'
        )
INSERT  INTO @tbl
        ( ID ,
          Doc ,
          ExpirationDate
        )
VALUES  ( 124 ,
          'Sertif' ,
          '2012-09-10 00:00:00.000'
        )


SELECT *
FROM 	
(SELECT ID,Doc,ExpirationDate FROM @tbl	) AS sourcetbl
PIVOT (MAX(ExpirationDate) FOR Doc IN ([CoreComp])) 
 AS Pivottbl

 

Open in new window

ID     CoreComp
123      2012-09-15 00:00:00.000
124      2012-06-22 00:00:00.000
0
 
LVL 8

Expert Comment

by:Crashman
ID: 38406693
and for the others columns too,

USE [Test]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[T](
	[ID] [int] NOT NULL,
	[Doc] [varchar](50) NOT NULL,
	[ExpirationDate] [datetime] NOT NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO



INSERT  INTO T
        ( ID ,
          Doc ,
          ExpirationDate
        )
VALUES  ( 123 ,
          'CoreComp' ,
          '2012-09-15 00:00:00.000'
        )
INSERT  INTO T
        ( ID ,
          Doc ,
          ExpirationDate
        )
VALUES  ( 123 ,
          'Derta' ,
          '2012-10-01 00:00:00.000'
        )
INSERT  INTO T
        ( ID ,
          Doc ,
          ExpirationDate
        )
VALUES  ( 123 ,
          'Sertif' ,
          '2012-09-10 00:00:00.000'
        )
INSERT  INTO T
        ( ID ,
          Doc ,
          ExpirationDate
        )
VALUES  ( 124 ,
          'CoreComp' ,
          '2012-06-22 00:00:00.000'
        )
INSERT  INTO T
        ( ID ,
          Doc ,
          ExpirationDate
        )
VALUES  ( 124 ,
          'Sertif' ,
          '2012-09-10 00:00:00.000'
        )


 DECLARE @columns VARCHAR(8000)
        SELECT @columns = COALESCE(@columns + ',[' + Doc  + ']',
                                 '[' + Doc + ']')
        FROM    T GROUP BY Doc

PRINT @columns


 DECLARE @Query VARCHAR(8000)
SET @Query = '
SELECT *
FROM 	
(SELECT ID,Doc,ExpirationDate FROM T) AS sourcetbl
PIVOT (MAX(ExpirationDate) FOR Doc IN ('+ @columns +')) 
 AS Pivottbl'
 
 EXECUTE(@Query)


 

Open in new window

0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 38406732
What is the selection criteria for an id?

123   2012-09-15 00:00:00.000

is neither the newest nor the oldest date.
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 25

Expert Comment

by:lwadwell
ID: 38408043
I probably completely misunderstood, but do you want:
    SELECT ID, ExpirationDate as CoreComp
    FROM your_table
    WHERE Doc = 'CoreComp'
0
 

Author Comment

by:lrbrister
ID: 38409298
Scotpletcher

The view is built with an outer apply, cross join etc so that literally the first 16 columns are all exactly the same.

Each ID (Not a primary key) that's replicated will have 16 individual records (Documents)
The dates are the expiration date for that particular document.
0
 
LVL 2

Accepted Solution

by:
Frank_Banin earned 2000 total points
ID: 38409851
to me it seem you need to do something as simply as this, unless I am not getting you either.

create  TABLE #table
    (
      ID INT ,
      Doc VARCHAR(50) ,
      ExpirationDate DATETIME
    )
INSERT INTO #table (id, doc, expirationdate)
VALUES      ( 123, 'CoreComp', '2012-09-15 00:00:00.000'),
            ( 123, 'Derta','2012-10-01 00:00:00.000' ),
            ( 123, 'Sertif', '2012-09-10 00:00:00.000' ),
            ( 124, 'CoreComp', '2012-06-22 00:00:00.000' ),
            ( 124, 'Sertif','2012-09-10 00:00:00.000' )
           
             

select max(ID) as ID, MAX(ExpirationDate)  as CoreComp
from #table
group by  Doc

--or

select max(ID)as ID, Doc, MAX(ExpirationDate) as ExpirationDate
from #table
group by  Doc
0
 

Author Closing Comment

by:lrbrister
ID: 38419831
Thanks. Sorry for the late get back
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

831 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