You usually use subqueries to create complex structures, although you can also use CROSS APPLY.

SELECT

1 AS SubmissionID,

1 AS OriginalUniqueSubmissionID,

'7-0' AS TestScenarioID,

0 AS CorrectedInd,

( SELECT

( SELECT 'TestData' AS BusinessNameLine1Txt FOR XML...

Something like the following:

-- do your first insert

INSERT ....

--conditionally do the second insert

IF @@ROWCOUNT = 0

INSERT ....

Here is the info about @@ROWCOUNT

Drew

sgmunson (3/10/2016)

---

drew.allen (3/10/2016)

---

sgmunson (3/10/2016)

---

I don't have a 2012 instance handy, or I could have tried using LEAD or LAG with some GROUP BYs, but here's a query that only uses...

sgmunson (3/10/2016)

---

I don't have a 2012 instance handy, or I could have tried using LEAD or LAG with some GROUP BYs, but here's a query that only uses 2008 syntax...

I think that you would be better off creating a view to do this than creating a computed column. In a view, you will be able to use FOR...

All of the solutions provided leave open the possibility of a trailing delimiter, which may not be desirable. This (UNTESTED) solution will make sure there are no leading or...

MMartin1 (3/8/2016)

---

Going deeper with the CROSS APPLY suggestion :

Consider something like this >

SELECT SUM(someNumberColumn) / (SELECT SUM(someNumberColumn) FROM Table) AS westCoastSalesAsA%OfTotal

FROM Table

WHERE someFilterColumn= 'west coast'

Could be done...

I also considered the following in case he wanted zeroes last, but the rest in ascending order (assuming that OwnStaffID is never negative).

ORDER BY ProjID, SIGN(OwnStaffID) DESC, OwnStaffID

Drew

To me, it looks like you want

ORDER BY ProjID, OwnStaffID DESC

This will sort by ProjID and within ProjID it will sort by OwnStaffID descending.

Dre

You have to remember that tables are sets and therefore inherently UNORDERED. You cannot depend on the presentation order to produce your desired results and there is no field...

You don't need to use cursors. As I said earlier, NTILE is your friend. Here is a solution that gets close. I'm dividing each major as evenly...

Please don't cross post. It fragments the thread. Please continue on this thread.

Drew

It would help tremendously if you posted your whole query instead of just the one part. That being said, it sounds like you are using an absolute path instead...

I'm very cautious about automatically updating records like this, because there is so much variation for entering the same information. For instance, addresses with apartment numbers. If you...

Sergiy (3/3/2016)

---

drew.allen (3/3/2016)

---

I see no reason to join to a Tally table here. You have to calculate (ID_No-1)/6 anyhow, so just use that as your first group by expression.

You can...