SQL PIVOT (I think!)

[cotton31]

I have some data that looks like this:

Address| Location
123 Fake St | Kitchen
123 Fake St | Bathroom
123 Fake St | Bedroom
123 Fake St | Lounge
56 New Road | Kitchen
56 New Road | Lounge
12 Another Lane | Bathroom
12 Another Lane | Lounge

I need it to look like this:

123 Fake St | Kitchen | Bathroom | Bedroom | Lounge
56 New Road | Kitchen | | | Lounge
12 Another Lane | | Bathroom | | Lounge

How would I do this? I thought I needed to use PIVOT, but all the examples I've found use aggregates on numbers (sum of sales etc.)

Cheers!

 

[cotton31]

I was afraid you'd say that...I don't know how many different locations there are (my example is very dumbed down!)

I could find out, but wouldn't like to write a subquery/cte for each possibility...

Any other ideas? Doesn't have to be massively efficient, as it's only a one off.

 

[cotton31]

Thanks - I'll give it a go!

Just to pick up on something you said - I don't have to have each location in a separate column, I'd be quite happy to have them all concatenated in one column with a delimiter, as long as I'm left with one row per address.

Alternatively, if this can be done in Excel with the original data set then I'm open to that, as it's going to end up there anyway!

 

[cotton31]

The first example is fine - is it possible?

 

[cotton31]

That's great topdog.

I've used XML PATH before, not sure why it didn't occur to me to use it this time! Suppose I didn't really think that the locations would be fine concatenated in one field.

On an unrelated question, I see you've cast to varchar(max) on the delimiter - I'd tend to specify the number of characters rather than use max. Are there any benefits to this?

Thanks for you help guys!