Probably down to...

tzanouch (5/27/2011)

---

I want to get the id's of everyone who has a specific value for every date.

ex. From the following table I want all those who have value='y' for every...

Thanks Craig,

Yes, I...

Craig, Thank you - that explains a lot, incredibly well!

I have taken that on board and now see why it was working like that.

Now for the *twist*.....

I just tried it...

Ninja's_RGR'us (5/25/2011)

---

Posted the bug about something really similar (different application but same source issue methinks)

https://connect.microsoft.com/SQLServer/feedback/details/383641/in-variable-concatenation-and-order-by-filters-results-like-where-condition

It was closed as "by design" and if it is the same issue then any of...

Craig Farrell (5/25/2011)

---

mister.magoo (5/25/2011)

---

Craig Farrell (5/25/2011)

---

I've got a bad feeling this has to do with repeated name in syscolumn and the apply function. I'm not done chewing on this...

Craig Farrell (5/25/2011)

---

I've got a bad feeling this has to do with repeated name in syscolumn and the apply function. I'm not done chewing on this one yet, just...

@Lamprey13 - yes that is probably the same thing as this - or at least they must be related.

So, I need to watch out for this one then.

I guess I...

mazzz (5/25/2011)

---

I don't know whether this would be equivalent to your query, but if I run the slightly altered code below I get blocks of heads (or tails) with the...

Michael Valentine Jones (5/24/2011)

---

I don't feel up to looking into what is going on with your query, but if you just want a random series, this is probably simpler and...

sandor.pakh (5/24/2011)

---

Thank you! Both, the pivot and the XQuery solutions work.

After some deliberation I've ended up using the XQuery solution.

Main reason being that I am not a fan of...

Have you tried setting NOCOUNT ON in the proc?

I have seen instances of client code using ADO that does not behave as expected due to the presence of row...

Borrowing Eddie's excellent sample data...here is an alternative method without the pivot

DECLARE @doc XML

SET @doc = N'<?xml version="1.0" encoding="UTF-16" ?>

<root name="TABLE_NAME_1">

<row action="U" timestamp="2011-05-18 17:14:21.000">

<column name="CRCUSN">86831</column>

<column name="CRSYER">111</column>

<column name="CRSFPD">1</column>

<column name="CRTSLD">3004.81</column>

<column name="CRTCRS">39.71</column>

<column name="CRFGAWD">148.21</column>

<column...

Arjun Sivadasan (5/23/2011)

---

use tempdb

go

if(object_id('tempdb..#test') is null) create table #test(ver varchar(50))

truncate table #test

insert into #test(ver)

values

('10.00.1600'),

('10.00.1763'),

('10.00.1779'),

('10.00.1787'),

('10.00.1798'),

('10.00.1806'),

('10.00.1812'),

('10.00.1818'),

('10.00.1823'),

('9.00.4315'),

('9.00.4317'),

('9.00.4325'),

('9.00.5000'),

('9.00.5254'),

('9.00.5259'),

('9.00.5266')

select * from...

Jeff Moden (5/23/2011)

---

Almost forgot... I've had people report the problem on all versions except 2008, so far. I don't believe they "fixed" it because it's actually not a bug....