May 9, 2012 at 11:57 am
Table named Clients with columns of ID,FirstName,LastName
I want a select statement that produces results with 2 columns
ID1,<client><firstname>Peter</firstname><lastname>Frampton</lastname></client>
Of course my table is a lot bigger, but help with the example I provided will get me going.
Thanks
May 9, 2012 at 12:20 pm
Please provide some sample data, table structure, business rules and expected result, like this format:
Data:
IF OBJECT_ID('tempdb..#t') IS NOT NULL
DROP TABLE #t
CREATE TABLE #T
(
ColA INT
,ColB INT
,ColC INT
)
INSERT INTO #T
SELECT 1 , 1, 10
UNION ALL SELECT 1 , 2, 11
UNION ALL SELECT 1 , 3, 12
UNION ALL SELECT 2 , 6, 20
UNION ALL SELECT 2 , 8, 21
UNION ALL SELECT 2 , 7, 22
UNION ALL SELECT 3 , 4, 31
UNION ALL SELECT 3 , 4, 32
UNION ALL SELECT 3 , 3, 33
Business Rlule : Get all the rows that has the MAX of ColB for each value of ColA.
Expected Result:
ColAColBColC
1312
2821
3431
3432
May 11, 2012 at 4:19 am
Do you mean something like this?
DECLARE @t TABLE (ID INT IDENTITY, FirstName VARCHAR(20), LastName VARCHAR(20))
INSERT INTO @t
SELECT 'Peter', 'Frampton'
UNION ALL SELECT 'Cold', 'Coffee'
UNION ALL SELECT 'Dwain', 'C'
SELECT ID
,(SELECT FirstName, LastName
FROM @t t2
WHERE t1.ID = t2.ID
FOR XML PATH ('client')) [xml]
FROM @t t1
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply