SQL Complex Select

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
Larry Bristersr. DeveloperAsked:
Who is Participating?
 
Frank_BaninConnect With a Mentor Commented:
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
 
CrashmanCommented:
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
 
CrashmanCommented:
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
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
Scott PletcherSenior DBACommented:
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
 
lwadwellCommented:
I probably completely misunderstood, but do you want:
    SELECT ID, ExpirationDate as CoreComp
    FROM your_table
    WHERE Doc = 'CoreComp'
0
 
Larry Bristersr. DeveloperAuthor Commented:
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
 
Larry Bristersr. DeveloperAuthor Commented:
Thanks. Sorry for the late get back
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.