Everyone that has posted in this thread could help. But you seem...

This works without leaving you open to SQL injection.

DECLARE @TableName AS NVARCHAR(20) = 'work', @sql AS NVARCHAR(MAX);

WITH p1 AS (

SELECT sqlStmt, ROW_NUMBER() OVER(ORDER BY ord, (SELECT NULL)) AS rn, col,...

jcrawf02 (3/7/2012)

---

Evil Kraig F (3/6/2012)

---

SQLRNNR (3/6/2012)

---

jcrawf02 (3/6/2012)

---

Evil Kraig F (3/6/2012)

---

And a week of troubleshooting later I'm back.

If, for some reason, you just can't seem to get the login for SSC...

Jeff Moden (3/7/2012)

---

I guess I don't understand why you need recursion for this at all. If the NEWID doesn't appear in the OLDID column, then it is a leaf...

R.P.Rozema (3/7/2012)

---

The Cad-method assumes that the newID always higher is than the oldID. This may very well be true, given that the ID is likely implemented as an identity column,...

Thanks to R.P.Rozema for the sample data that the OP "forgot" 😉

Here's another way:

WITH CTE

AS (SELECT OldID, NewID, NewID AS CurrentID

FROM #cref

UNION...

I missed this thread until this morning, I want to play 😀

Here's a bit of a mathematical answer to pivoting: -

SELECT

SUM(eachGroup) AS Total,

SUM(eachGroup*(1-ABS(SIGN(IGroup-1)))) AS Group1,

SUM(eachGroup*(1-ABS(SIGN(IGroup-2)))) AS Group2,

SUM(eachGroup*(1-ABS(SIGN(IGroup-3)))) AS Group3,

SUM(eachGroup*(1-ABS(SIGN(IGroup-4))))...

Paste exactly what you tried. There is no syntax error in either of the code snippets I posted.

jcb (3/6/2012)

---

Well, I wrote a more generic solution to any user table.

create table TESTE (PK int, C1 char(1), C2 char(1), C3 char(1), C4 char(1), C5 char(1))

GO

insert into TESTE values

(1, 'a',...

nilesh k (3/6/2012)

---

I want to grab many of the columns including 'posted D/T' column from this table and i do not want to write individual column name in select query.

It...

nilesh k (3/6/2012)

---

Thanks for the reply.

What if there are n number of columns n table with ID as unique key?

Select * , MAX[Posted D/T] from MyTable

It is giving me below...

Like this: -

nilesh k (3/6/2012)

---

I have below fields in sql table

segment Posted D/T

Test 1 06/06/2012 10:00 AM

Test 1 06/06/201210 11:00...

Only way I can think of would be to use a CLR. Do a try catch block to convert to XML, if it fails then return 0 if it passes...

Nope, the OP wanted to be able to only select the column if the data is not null and originally wanted the solution...