Query Help

Question

Query Help

SLLRDK

Old Hand

Points: 384
More actions
July 31, 2009 at 7:01 am

#212959

Hi,
I have a simple query that returns the data like this:
ID Title Fname Sname
2261Ms Tamami Ito
2261Ms Maho Shimizu
2261Ms Makiko Umemura
I would like to be able to change the format so it reads:
ID Title Fname Sname Title2 Fname2 Sname2 Title3 Fname3 Sname3
2261Ms Tamami Ito Ms Maho Shimizu Ms Makiko Umemura
There will be multible ID's in the result set and it needs to format each ID in the same way.
Hope this makes sense and if anyone has any ideas on the best way to achieve this I would be most greatful.
I've been experiemnting with XML and PIVOT but have not yet managed to work it out.
Cheers for any suggestions.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Allister Reid SSCrazy Points: 2666 More actions · Answer 1

Hi,

here is a solution that relies on a tally table http://www.sqlservercentral.com/articles/T-SQL/62867/ and is based upon the code in http://www.sqlservercentral.com/articles/Crosstab/65048/, these are two excellent articles, and I recommend them both highly, very useful.

For your convenienc, if you require a tally table:

--==========================================================================

-- Create and populate a Tally table

--=============================================================================

--===== Conditionally drop

IF OBJECT_ID('dbo.Tally') IS NOT NULL

DROP TABLE dbo.Tally

--===== Create and populate the Tally table on the fly

SELECT TOP 11000 --equates to more than 30 years of dates

IDENTITY(INT,1,1) AS N

INTO dbo.Tally

FROM Master.dbo.SysColumns sc1,

Master.dbo.SysColumns sc2

--===== Add a Primary Key to maximize performance

ALTER TABLE dbo.Tally

ADD CONSTRAINT PK_Tally_N

PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100

--===== Let the public use it

GRANT SELECT, REFERENCES ON dbo.Tally TO PUBLIC

And here is a solution to your problem:

USE tempdb

IF OBJECT_ID('#temp') IS NOT NULL

DROP TABLE #temp

CREATE TABLE #temp (id int, title varchar(10), fname varchar(20), sname varchar(30))

INSERT INTO #temp

SELECT 2261, 'Ms', 'Tamami', 'Ito' UNION ALL

SELECT 2261, 'Ms', 'Maho', 'Shimizu' UNION ALL

SELECT 2261, 'Ms', 'Makiko', 'Umemura' UNION ALL

SELECT 2262, 'Mr', 'Joe', 'Bloggs' UNION ALL

SELECT 2262, 'Mr', 'John', 'Smith'

--SELECT * FROM #temp

--Need to know what the maximum number of names are associated with an id

DECLARE @MaxNumNames INT

SELECT @MaxNumNames = MAX(cnt) FROM (SELECT COUNT(*) AS cnt FROM #temp GROUP BY id) C

--Create a variable to hold dynamic SQL; the query needs to be dynamic because we don't know

--up-front what the maximum number of names that are associated with an id

DECLARE @sql VARCHAR(MAX)

--Initialise dynamic SQL variable

SET @sql = 'SELECT

id,'

--Add a title, fname and sname column from 1 to @MaxNumNames

SELECT @sql = @sql + '

MAX(CASE WHEN row = ' + CONVERT(VARCHAR(4), tally.N) --Append numeric suffix to column name

+ ' THEN title ELSE NULL END) AS title' + CONVERT(VARCHAR(4), tally.N) +',

MAX(CASE WHEN row = ' + CONVERT(VARCHAR(4), tally.N)

+ ' THEN fname ELSE NULL END) AS fname' + CONVERT(VARCHAR(4), tally.N) + ',

MAX(CASE WHEN row = ' + CONVERT(VARCHAR(4), tally.N)

+ ' THEN sname ELSE NULL END) AS sname' + CONVERT(VARCHAR(4), tally.N) + ','

FROM tally

WHERE N <= @MaxNumNames

ORDER BY N

--Stuff removes the final comma and then append the FROM sub-query. The sub-query takes

--original data and adds a row number from 1 to number of names for each id

SELECT @sql = STUFF(@SQL, LEN(@SQL), 1, '') + '

FROM

(

SELECT id, title, fname, sname,

ROW_NUMBER() OVER (PARTITION BY id ORDER BY sname, fname) AS row

FROM #temp

) A

GROUP BY id'

--Output dynamic SQL to Messages window

PRINT @sql

--Execute the dynamic SQL

EXEC (@SQL)

///Edit - added comments, fixed error in tally table code

SLLRDK Old Hand Points: 384 More actions · Answer 2

Thanks for quick response. I will have a look at the articles and your code and see what I can achieve.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

Allister Reid (7/31/2009)
Hi,
here is a solution that relies on a tally table http://www.sqlservercentral.com/articles/T-SQL/62867/ and is based upon the code in http://www.sqlservercentral.com/articles/Crosstab/65048/, these are two excellent articles, and I recommend them both highly, very useful.

Heh... thanks again for the plugs, Allister. :blush:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Allister Reid SSCrazy Points: 2666 More actions · Answer 4

They are two articles I go back to most often here Jeff, thank you.

///Edit - really off-topic, but it would probably be one I would end up constantly referencing - how is the quirky update article re-write going?

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 5

Slow... I guess I'm trying to juggle too many things at one time. I need to concentrate on "one" of something and get it done. The running total/quirky update article would probably be a good one for me to prioritize since I'm only about 6 months behind on that. 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Allister Reid SSCrazy Points: 2666 More actions · Answer 6

heh he... I know that feeling, I'm currently hiding out on scc from a overdue web project... I tried to convince myself that I needed to brush up on dynamic cross-joins to get it done 😉