SELECT
ID,

CASE 
	WHEN AttrID = '4' THEN Valdate END as Valdate,  
Case
	WHEN AttrID = '7' THEN ValInt END as Valstr,  
Case
	WHEN AttrID = '10' THEN ValStr END as Valint  

FROM dbo.llattrdata A1

Group BY ID

SELECT ID,
       CASE 
            WHEN AttrID = '4' THEN MAX(Valdate)
       END AS Valdate,
       CASE 
            WHEN AttrID = '7' THEN MAX(ValInt)
       END AS Valstr,
       CASE 
            WHEN AttrID = '10' THEN MAX(ValStr)
       END AS Valint
FROM   dbo.llattrdata A1
GROUP BY
       ID

SELECT ID,
       MAX(Valdate) AS Valdate,
       MAX(ValInt) AS Valstr,
       MAX(ValStr) AS Valint
FROM   dbo.llattrdata A1
GROUP BY
       ID

quote:

---

Originally posted by musclebreast

Hello,

I hope you can help me.

I've got one Table with the following columns:

ID.........ATTRID......Valdate.......ValStr....ValINT
1234........4...........2012/8/8......Null......NULL
1234........7..........Null............5........NULL
1234........10.........NULL...........NULL.......day

I need to filte it in one Row (ID is the same):

ID....Valdate.....Valstr....ValINT
1234...2012/8/8.....5.........day


I tried it as followed:

SELECT
ID,

CASE 
	WHEN AttrID = '4' THEN Valdate END as Valdate,  
Case
	WHEN AttrID = '7' THEN ValInt END as Valstr,  
Case
	WHEN AttrID = '10' THEN ValStr END as Valint  

FROM dbo.llattrdata A1

Group BY ID

I get the following error:

Column 'dbo.llattrdata.AttrID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

How can I solve the problem?

Kind regards,

Lara

---

quote:

---

Originally posted by sunitabeck

Change it to this:

SELECT ID,
       CASE 
            WHEN AttrID = '4' THEN MAX(Valdate)
       END AS Valdate,
       CASE 
            WHEN AttrID = '7' THEN MAX(ValInt)
       END AS Valstr,
       CASE 
            WHEN AttrID = '10' THEN MAX(ValStr)
       END AS Valint
FROM   dbo.llattrdata A1
GROUP BY
       ID

If your sample data is really representative of your actual data - i.e., all the rows except one are nulls for each of the columns of interest, you could even do this:

SELECT ID,
       MAX(Valdate) AS Valdate,
       MAX(ValInt) AS Valstr,
       MAX(ValStr) AS Valint
FROM   dbo.llattrdata A1
GROUP BY
       ID

---

SELECT ID,
       MAX(Valdate) AS Valdate,
       MAX(ValInt) AS Valstr,
       MAX(ValStr) AS Valint
FROM   dbo.llattrdata A1
GROUP BY
       ID

select id
      ,max(case when attrid='4' then valdate
                else null
           end
          ) as valdate
      ,max(case when attrid='7' then valstr
                else null
           end
          ) as valstr
      ,max(case when attrid='10' then valint
                else null
           end
          ) as valint
      ,max(case when attrid='11' then valint
                else null
           end
          ) as valint1
  from dbo.llattrdata
 group by id

quote:

---

Originally posted by musclebreast

Hi,

thanks. I learned something:)Your second solutions works:

SELECT ID,
       MAX(Valdate) AS Valdate,
       MAX(ValInt) AS Valstr,
       MAX(ValStr) AS Valint
FROM   dbo.llattrdata A1
GROUP BY
       ID

But not the first one and I don't get because it's the same statement except the

CASE
WHEN AttrID = '4'

term. It seems here is the problem. I get the following error:

Column 'dbo.llattrdata.AttrID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

What might be the problem here?

I really need to know it, because I need the when-statement. The reason is that the column Valint has two Values in two rows which I can filter through the AttrID. Take a look at the Table:

ID.........ATTRID......Valdate.......ValStr....ValINT
1234........4...........2012/8/8......Null......NULL
1234........7..........Null............5........NULL
1234........10.........NULL...........NULL.......day
1234........11.........NULL..........NULL........end of quarter

I need to filter it in one Row (ID is the same):

ID....Valdate.....Valstr....ValINT....ValInt1
1234...2012/8/8.....5.........day......end of quarter

Kind regards,

Lara

---

SELECT
ID,

MAX(CASE 
	WHEN AttrID = '4' THEN Valdate END) as Valdate,  
MAX(Case
	WHEN AttrID = '7' THEN ValInt END) as Valstr,  
MAX(Case
	WHEN AttrID = '10' THEN ValStr END) as Valint  

FROM dbo.llattrdata A1

Group BY ID

quote:

---

Here is the problem:

MAX(Case
WHEN AttrID = '10' THEN ValStr END) as Valint
MAX(Case
WHEN AttrID = '11' THEN ValStr END) as Valint1

I'got two different rows, but the same column...I get the following error:

Msg 102, Level 15, State 1, Line 10
Incorrect syntax near 'MAX'.

---

MAX(Case
	WHEN AttrID = '10' THEN ValStr END) as Valint,
MAX(Case
	WHEN AttrID = '11' THEN ValStr END) as Valint1

SELECT *
FROM
(
SELECT *
FROM dbo.llattrdata A1
Where ID = '434473'
)t
PIVOT (MAX(ValStr) FOR AttrID IN ([5],[7],[10],[11]))p

;With QuarterTable(EOQDate)
AS
(SELECT DATEADD(qq,1,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)) -1
 UNION ALL
 SELECT DATEADD(qq,1,EOQDate)
 FROM QuarterTable
 WHERE DATEADD(qq,1,EOQDate) <= DATEADD(yy,DATEDIFF(yy,0,GETDATE())+1,0)
)

SELECT DataID,DATEADD(dd,CASE WHEN period = 'week(s)' then [time] * 7 WHEN period = 'day(s)' THEN [time] END,q.EOQDate)
FROM YourTable t
CROSS JOIN QuarterTable q

;With QuarterTable(EOQDate)
AS
(SELECT DATEADD(qq,1,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)) -1
UNION ALL
SELECT DATEADD(qq,1,EOQDate)
FROM QuarterTable
WHERE DATEADD(qq,1,EOQDate) <= DATEADD(yy,DATEDIFF(yy,0,GETDATE())+1,0)
), HalfTable1(EOQDate)
AS
(SELECT DATEADD(qq,2,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)) -1
UNION ALL
SELECT DATEADD(qq,2,EOQDate)
FROM HalfTable1
WHERE DATEADD(qq,2,EOQDate) <= DATEADD(yy,DATEDIFF(yy,0,GETDATE())+1,0)
)

--your select query using QuarterTable and HalfTable1

;With QuarterTable(EOQDate)
AS
(SELECT DATEADD(qq,1,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)) -1
UNION ALL
SELECT DATEADD(qq,1,EOQDate)
FROM QuarterTable
WHERE DATEADD(qq,1,EOQDate) <= DATEADD(yy,DATEDIFF(yy,0,GETDATE())+1,0)
), HalfTable1(EOQDate)
AS
(SELECT DATEADD(qq,2,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)) -1
UNION ALL
SELECT DATEADD(qq,2,EOQDate)
FROM HalfTable1
WHERE DATEADD(qq,2,EOQDate) <= DATEADD(yy,DATEDIFF(yy,0,GETDATE())+1,0)
)

SELECT *
FROM my table as tester 
CROSS JOIN HalfTable1 
WHERE tester.End_Spec = 'end of half-year' 

UNION ALL

SELECT *
FROM my table as tester 
CROSS JOIN QuarterTable
WHERE tester.End_Spec = 'quarter'