Antediluvian

WITH a (PID,VisitType,Rn,RowNo)

AS (

SELECT PID,VisitType,Rn

,ROW_NUMBER() OVER (PARTITION BY PID,Rn ORDER BY VisitType ASC)

FROM @PatDev

),

b (PID,MaxRowNo)

AS (

SELECT PID,MAX(RowNo)

FROM a

GROUP BY PID

)

SELECT b.PID

,MAX(CASE WHEN c.C = 1 THEN ISNULL(a2.VisitType,a.VisitType) END) AS [Col1]

,MAX(CASE...

Is there a specific reason why separate tables?

Nice catch Kevin 🙂

DECLARE @InvoiceNo int = ISNULL((SELECT MAX(o.InvoiceNo) FROM Orders o),0);

WITH cte (ID,InvoiceNo)

AS (

SELECTo.ID,@InvoiceNo+ROW_NUMBER() OVER (ORDER BY o.ID ASC)

FROMOrders o

JOIN SetupAccountTypes a

ON a.ID = o.AccountTypeID

AND a.AccountType = 'COD'

AND o.DeliveryDate >= '20160101'

AND o.DeliveryDate...

First you need to allocate a unique number to each row

Second use Jeff Moden's DelimitedSplit8K function (here) to split the data

Third use PIVOT or CASE statements to pivot...

Lowell (5/16/2016)

---

Complete non-sequitur here guys, but I'm rather pleased with myself.

I just got my MCSA after taking 70-463 Implementing a Data Warehouse in SQL Server 2012/2014.

I had set a mental...

Create a new report, add data source and dataset

Drag all four columns (ID Seller Hyderabad Bangalore) to detail box

continue on to finish report

Double click on Details_Group under Row Groups

Click Add...

KMM-489657 (11/15/2007)

---

I was just reading thru this thread and I have a similar issue. I'm trying to extract the number string from a StarRating of Hotels column. For example the...

Well there have been ups and downs but I weathered the storms and even survived some...

Grumpy DBA (5/4/2016)

---

^ I agree with Brandie. Matter of fact that's how I came to my current employer, a former colleague has been here for 8 years and enjoys...

Glad I got a channel now to watch it, missed it for ages.

Watched a replay of the 5 hour marathon of the Red Sox / Astros game.

Thrilling game could not...

Ed Wagner (4/25/2016)

---

Another good one is to say that someone's "out to lunch". Well, I've taken that a step further and say that someone's "so out to lunch -...

Hey, I resemble that remark :crazy:

Add another column to signify row type or ordering and union all to join the two sets of data, i.e.

SELECT 1 AS [RowType],

'Brockton Hospital' AS [col1],

...