Jeff Moden (9/16/2009)

---

Matt is certainly on the right track... but you don't need a CASE statement...

select A.grading, ISNULL(NULLIF(count(B.IDno),0),COUNT(B.IDno)) as myCount

From A

left join B on A.fkIDno = B.IDno and A.grading =...

select column1,column2 as "SUBTAG/column2" ,column3 from table1 FOR XML PATH('')

Also you can use INTERSECT and EXCEPT to find row matches

SELECT LEN(@String)-LEN(REPLACE(@String,'@',''))

peterzeke (9/2/2009)

---

Argh! Mark's solution almost works....

I've discovered a scenario where the distinct EpisodeGroup misses the mark. Below is the sql script to create/populate the sample data that ends up with...

Try this

SELECT ROWNUM,FROM_DATE,TO_DATE,CNT_DAYS,LESS_THAN_60,

ROW_NUMBER() OVER(ORDER BY FROM_DATE)-

ROW_NUMBER() OVER(PARTITION BY LESS_THAN_60 ORDER BY FROM_DATE) AS EpisodeGroup

FROM #tbl_EpisodeGroup

ORDER BY...

As well as comma delimited strings, you can use XML

Pakki (8/31/2009)

---

Mark (8/31/2009)

---

naveenreddy.84 (8/31/2009)

---

Please find below the table creation and Insert Script

CREATE TABLE Sequence

(ID INT,

Seq VARCHAR(MAX))

INSERT INTO Sequence VALUES(1,'01111')

INSERT INTO Sequence VALUES(1,'10111')

INSERT INTO Sequence VALUES(1,'11011')

INSERT INTO Sequence VALUES(1,'11101')

INSERT...

naveenreddy.84 (8/31/2009)

---

Please find below the table creation and Insert Script

CREATE TABLE Sequence

(ID INT,

Seq VARCHAR(MAX))

INSERT INTO Sequence VALUES(1,'01111')

INSERT INTO Sequence VALUES(1,'10111')

INSERT INTO Sequence VALUES(1,'11011')

INSERT INTO Sequence VALUES(1,'11101')

INSERT INTO Sequence...

rekha_sara (8/31/2009)

---

Thank u so much Mark!!

one doubt..Will CASE work the same as DECODE in case of nulls or should we add like

CASE WHEN c1.name IS NULL THEN NULL

...

Something like this?

CASE WHEN c1.name IS NULL THEN NULL

ELSE CASE WHEN c1.first_name IS NULL THEN c1.name

...

TT (8/13/2009)

---

I am looking to trim in the CROSS APPLY part. I getting XML that has a space after the value "loannumber" value in the NAME element.

CROSS APPLY Breq.nodes('DOCUMENT/FIELD/VALUE[../NAME/text()="loannumber"]')...

TT (8/13/2009)

---

Is there a trim function that I could use for the "loannumber"?

SELECT Doc.value('NAME[1]', 'varchar(16)') AS [Name],

Doc.value('VALUE[1]', 'varchar(16)') AS [Value],

...

Try this

SELECT Doc.value('NAME[1]', 'varchar(16)') AS [Name],

Doc.value('VALUE[1]', 'varchar(16)') AS [Value],

Num.value('.','int') AS LoanNumber

FROM @xml.nodes('/BATCH/ORDERREQUEST') AS B(Breq)

CROSS APPLY Breq.nodes('DOCUMENT/FIELD[NAME/text()!="loannumber"]')...

Untested...

SELECT ProductDetail.Productid, product.classificationNo, product.name 'name', product.maker, product.yrmade,

(case

when yrmadeid IN (100,200,300) then 'latest version'

else 'Old version'

end) 'version',

COUNT(*) OVER(PARTITION BY product.name),

ROW_NUMBER() OVER(PARTITION BY product.name ORDER BY ProductDetail.Productid)

FROM

ProductDetail

JOIN...