HI,

If you do a search for SQL Cross Tab queries you should find loads of examples.

They will involve CASE statements

Thanks

Chris

I agree the second one is much better on the eye

thanks rbarryyoung

I'll wait and see if anyone comes up with someone while I'll researching this a bit more 🙂

mmm

Ok lets see if I can explain this.

SELECT COUNT(DISTINCT NAME)

This will return the count of the distinct names regardless of duplicates

e.g

ROW NAME

1 ...

Sorry I forgot the Identity(1,1) on the create table... 😉

Thanks for that.

What I really want to know is:

IS there a major difference between controlling the Isolation using a SET statement or using a HINT?

thanks

Chris

HI,

TRy looking at the OVER() function in Books on line, this will put you in the write direction as it will give you a ROW number you can self join...

something like this:

DECLARE @PNo numeric (10,0)

DECLARE @counter INT

DECLARE @counterMAX INT

CREATE TABLE #PT

(ROWNUM INT PRIMARY KEY

PT datetime,

...

Have you tried using a @counter variable that you inciment instead of running a select in you while clause?

thanks

Chris

hi,

This is a basic query:

SELECT *

FROM MyTable

WHERE ID IN

(SELECT ID

FROM MyTable

GROUP BY ID

HAVING COUNT(*) >1)

The nested select can be changed to a Join/Derived table if you wish, I just...

HI,

Have you tried looking up the use of Tally tables which will give you a continuious list of running dates and then applying your logic to that?

thanks

Chris

TRy this...

select name,lid,ISNULL([20S],0) as '20s',ISNULL([40S],0) as '40s'

from

(

Select

name

,lid

,equipgrp

,ISNULL(cnt ,0) as cnt

from #tmptrad

) ps

PIVOT

(

sum(cnt) for equipgrp in ([20S],[40S])

)AS pvt

I change the values of the result set with the ISNULL...

HI,

If only you were using SQL2005 you could use the new PIVOT functionality.

However you are going to have to use Case statements to Pivot your query.

Do a search for cross...

CHECK DUMMY IN GROUP

AUTHORIZATION DENY

TRY SAVE DUMMY NOT

HEre is an example:

DECLARE @MyTable TABLE

(Vendor VARCHAR(10),

Name1 VARCHAR(10),

Name2 VARCHAR(10),

Name3 VARCHAR(10),

Name4 VARCHAR(10),

Name5 VARCHAR(10))

INSERT INTO @MyTable

SELECT 'Vendor1','NameA','NameB','NameC','NameD','NameE'

SELECT DISTINCT

Vendor

,[Name]

FROM

(

SELECT Vendor ,Name1,Name2,Name3,Name4,Name5

FROM @MyTable

) pvt

UNPIVOT ([Name] FOR Header IN ([Name1],[Name2],[Name3],[Name4],[Name5])) unpvt

Just remember that if...