How about this?

; with TableCTE AS

(

SELECT *

FROM

( VALUES

(1, 3, 10, 'Red')

,(2, 3, 11, 'Red')

,(3, 3, 11, 'Red')

,(4, 3, 15, 'Blue')

,(5, 4, 10, 'Red')

,(6, 4, 11, 'Red')

,(7,...

did u try the way i posted?

THAT was a magical code 🙂 Thanks Paul..

ChrisM@Work (7/7/2011)

---

SQLkiwi (7/7/2011)

---

Hi Chris,

There's no need for a join at all - a point I realised after editing the TOP trick into your code. I have updated the code...

Something on the lines of this:

select *

from yourtable

where ( col1 + col2 + col3 ....... + col50 ) is not null

Concatenation of any value to a NULL yields NULL...

You can try something like this:

select top 100 name , CountOfRow = COUNT(*) OVER( PARTITION BY 1)

from sys.columns

I remember answering to a question very similar to this one. I already see that u got ur reply, but here is (are) another way of doing it:

declare @t table...

Paul-202125 (6/28/2011)

---

The last row returned from your SQL is:

StartDate EndDate ChargeAmount

2007-05-30 2008-03-31 ...

How about this?

; WITH UnpivotTable AS

(

SELECT CustomerId , Dates , Cols , ChargeAmount

, RN = ROW_NUMBER() OVER( ORDER BY Dates ASC)

FROM

(

SELECT

CustomerId

,ChargeType

,StartDate...

Yep, i know how to do this; But i want more information from you. I need to get the DDLs, DMLs for samples tables, sample table data , indexes etc..Please...

u want all the 67 rows to have the row count value?

Something like this?

select

SUM ( case when item <= 100 then 1 else 0 end) as [Below100count],

SUM ( case when item > 100 and item <= 500 then 1 else...

You hit the "Submit" button before u completed the question, right? i dont find the requirement there :w00t:

Like this:

; with cte (items) as

( select 'line1'

union all select 'line2'

union all select 'line3'

union all select 'line4'

)

select ','+items

from cte

for xml path(''), type

How about this?

; with diffenrentiator as

(

SELECT s.Rowid sRowID, s.SRC sSRC , s.OrgNo sOrgNo , s.Sdate sSate

,s.Edate sEdate , t.*,

...