Help with Transposing Data - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help with Transposing Data

Author

Topic

MSwanston
Starting Member

23 Posts

Posted - 2009-08-10 : 05:02:34

Hi
I have a query which returns a list of team members, with an indicator as to who is the team leader (scheme actuary). There are no fixed number of team members, and not always a scheme actuary, but have been asked if the data can be transposed so there is only one row per Company/Scheme, with columns SchemeActuary, Assistant1, Assistant2, Assistant3...Assistantx.
This is my current SQL query:


SELECT DISTINCT S.CompanyID, SA.SchemeID, S.SchemeName, SA.Assistant, SA.SchemeActuary 
FROM dbo.SchemeAssistants AS SA 
INNER JOIN dbo.Schemes AS S ON SA.SchemeID = S.SchemeID 
INNER JOIN dbo.Valuations AS V ON S.SchemeID = V.SchemeID 
INNER JOIN dbo.Companies AS C ON S.CompanyID = C.CompanyID 
WHERE (V.ValuationDate >= CONVERT(DATETIME, '2005-01-01 00:00:00', 102)) AND (V.ValuationDate <= CONVERT(DATETIME, '2008-12-31 00:00:00', 102)) 
ORDER BY SA.SchemeID

Which returns the number of team members per scheme, so for example for one scheme I will get two rows, and another 6 rows, and another 4. Is it possible for the data to be transposed into:
CompanyID
SchemeID
SchemeName
SchemeActuary
Assistant1
Assistant2
...
Assistantx
If you need any firther info on the table structures, please shout.
Thanks in advance
Martin

Sachin.Nand

2937 Posts

Posted - 2009-08-10 : 06:16:25

U mean Assitant1,Assitant2..Assitantx shud be columns???

PBUH

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-08-10 : 06:19:42

Look up the PIVOT command.

This may help:
http://technet.microsoft.com/en-us/library/ms177410.aspx

Sachin.Nand

2937 Posts

Posted - 2009-08-10 : 06:32:55

You need dynamic pivot.Have a look at this
http://www.sqlteam.com/article/dynamic-cross-tabs-pivot-tables

PBUH

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-08-10 : 07:31:25

r post sample data and required output?
see this link too
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx

MSwanston
Starting Member

23 Posts

Posted - 2009-08-10 : 08:57:33

quote:
Originally posted by bklr

r post sample data and required output?
see this link too
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx

Thanks for the links - am relatively inexperienced in SQL, having only really worked with SELECT/INSERT/UPDATE and JOINS, but will try and get my head around how I can implement this.
As for sample data/results, I have created before and after data in Excel (as am not allowed to show the actual data) but am sure how I can post it here.
Thanks again
Martin

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-08-10 : 09:10:33

Sorry to post another link, but it is internal to this site and wil answer your question about how to post sample data (and maybe many other questions you may have).

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210

MSwanston
Starting Member

23 Posts

Posted - 2009-08-10 : 09:25:57

quote:
Originally posted by RickD

Sorry to post another link, but it is internal to this site and wil answer your question about how to post sample data (and maybe many other questions you may have).

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210

Hi Rick
Sorry

am probably being dumb but cannot find anything in that post about how to post sample data.
Thanks for your help
Martin

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-08-10 : 11:00:04

The first 2 links under the How To section.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-08-10 : 11:00:05

The first 2 links under the How To section.

MSwanston
Starting Member

23 Posts

Posted - 2009-08-10 : 12:59:46

quote:
Originally posted by RickD

The first 2 links under the How To section.

Thanks, I always think what I see is the the result, but obviously not when you use code blocks!
OK, this is my current data:


CompanyID SchemeID	SchemeName	Assistant	SchemeActuary
1	  1	        Scheme 1	John.Doe	0
1	  1	        Scheme 1	Jo.Blogs	1
1	  1	        Scheme 1	Michael.Smith	0
1	  1	        Scheme 1	John.Williams	0
1	  1	        Scheme 1	Mark.White	0
1	  1	        Scheme 1	Richard.Purple	0
3	  3	        Scheme 2	Julie.Green	0
3	  3	        Scheme 2	Dawn.Gold	1
3	  4	        Scheme 3	Robert.Brown	0
3	  4	        Scheme 3	William.Red	1
3	  4	        Scheme 3	Alf.Black	0
3	  4	        Scheme 3	Marcus.Damson	0
3	  4	        Scheme 3	Julie.Green	0
3	  5	        Scheme 4	Bill.Smith	0
3	  5	        Scheme 4	John.Thomas	0
3	  5	        Scheme 4	Archie.Hill	0
4	  6	        Scheme 5	Adam.Jordan	0
4	  6	        Scheme 5	Susan.Smith	0
4	  6	        Scheme 5	Michael.Yellow	1
4	  6	        Scheme 5	Albert.Adams	0

And this is how I'd like to see the data:


CompanyID SchemeID SchemeName	SchemeActuary	Assistant1	Assistant2	Assistant3	Assistant4	Assistant5
1	  1	   Scheme 1	Jo.Blogs	John.Doe	Michael.Smith	John.Williams	Mark.White	Richard.Purple
3	  3	   Scheme 2	Dawn.Gold	Julie.Green				
3	  4	   Scheme 3	William.Red	Robert.Brown	Alf.Black	Marcus.Damson	Julie.Green	
3	  5	   Scheme 4	Bill.Smith	John.Thomas	Archie.Hill		
4	  6	   Scheme 5	Michael.Yellow	Adam.Jordan	Susan.Smith	Albert.Adams

I hope my request now makes more sense!
Thanks
Martin

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-08-11 : 02:43:47

Hi Try this once,


SELECT COMPANYID,SCHEMEID,SCHEMENAME,CASE WHEN SCHEMEACTUARY = 1 THEN ASSISTANT  END SCHEMEACTUARY,ASSISTANT,
ROW_NUMBER() OVER( PARTITION BY COMPANYID,SCHEMEID ORDER BY SCHEMENAME) AS RID 
INTO #DETAILS FROM @T WHERE SCHEMEACTUARY <> 1

SELECT 
	COMPANYID,SCHEMEID,SCHEMENAME, MAX([1]) AS "ASSISTANT1", MAX([2]) AS "ASSISTANT2", MAX([3]) AS "ASSISTANT3", 
	MAX([4]) AS "ASSISTANT4", MAX([5]) AS "ASSISTANT5"			
INTO #DETAILS1
FROM #DETAILS		
PIVOT(MAX(ASSISTANT) FOR RID IN ( [1], [2], [3], [4], [5]))P
GROUP BY SCHEMEID,SCHEMENAME,COMPANYID

SELECT DISTINCT D.COMPANYID,D.SCHEMEID,D.SCHEMENAME,MAX(CASE WHEN D.SCHEMEACTUARY = 1 THEN D.ASSISTANT  END) SCHEMEACTUARY,D1.ASSISTANT1,ASSISTANT2,ASSISTANT3,ASSISTANT4,ASSISTANT5 FROM #DETAILS1 D1
INNER JOIN @T AS D ON D.COMPANYID = D1.COMPANYID AND D.SCHEMEID = D1.SCHEMEID AND D.SCHEMENAME = D1.SCHEMENAME
GROUP BY D.COMPANYID,D.SCHEMEID,D.SCHEMENAME,D1.ASSISTANT1,ASSISTANT2,ASSISTANT3,ASSISTANT4,ASSISTANT5

MSwanston
Starting Member

23 Posts

Posted - 2009-08-11 : 04:16:28

quote:

Originally posted by Nageswar9

Hi Try this once,


SELECT COMPANYID,SCHEMEID,SCHEMENAME,CASE WHEN SCHEMEACTUARY = 1 THEN ASSISTANT  END SCHEMEACTUARY,ASSISTANT,
ROW_NUMBER() OVER( PARTITION BY COMPANYID,SCHEMEID ORDER BY SCHEMENAME) AS RID 
INTO #DETAILS FROM @T WHERE SCHEMEACTUARY <> 1

SELECT 
	COMPANYID,SCHEMEID,SCHEMENAME, MAX([1]) AS "ASSISTANT1", MAX([2]) AS "ASSISTANT2", MAX([3]) AS "ASSISTANT3", 
	MAX([4]) AS "ASSISTANT4", MAX([5]) AS "ASSISTANT5"			
INTO #DETAILS1
FROM #DETAILS		
PIVOT(MAX(ASSISTANT) FOR RID IN ( [1], [2], [3], [4], [5]))P
GROUP BY SCHEMEID,SCHEMENAME,COMPANYID

SELECT DISTINCT D.COMPANYID,D.SCHEMEID,D.SCHEMENAME,MAX(CASE WHEN D.SCHEMEACTUARY = 1 THEN D.ASSISTANT  END) SCHEMEACTUARY,D1.ASSISTANT1,ASSISTANT2,ASSISTANT3,ASSISTANT4,ASSISTANT5 FROM #DETAILS1 D1
INNER JOIN @T AS D ON D.COMPANYID = D1.COMPANYID AND D.SCHEMEID = D1.SCHEMEID AND D.SCHEMENAME = D1.SCHEMENAME
GROUP BY D.COMPANYID,D.SCHEMEID,D.SCHEMENAME,D1.ASSISTANT1,ASSISTANT2,ASSISTANT3,ASSISTANT4,ASSISTANT5

Thanks...but it can't find a reference to @T, and as most of your SQL is new to me, am not quite sure where it belongs!
I do really appreciate your help - thanks.
Martin

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-08-11 : 04:37:49

replace @t with ur tablename and run the query

MSwanston
Starting Member

23 Posts

Posted - 2009-08-11 : 05:29:41

Thanks, but based on the original query, there are a few other tables that are joined that don't appear to be listed, and as have stated, it's a bit over my head!
Martin

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-08-11 : 06:55:36

#details, #details1 are temporary tables . Please go through once.

MSwanston
Starting Member

23 Posts

Posted - 2009-08-11 : 07:00:14

quote:
Originally posted by Nageswar9

#details, #details1 are temporary tables . Please go through once.

OK, will try but the more I look, the more confused I get!
Thanks anyway.
Martin

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-08-11 : 07:02:16

if u want dynamically, to build columns try this below


DROP TABLE #DETAILS

DECLARE @T TABLE(COMPANYID INT,SCHEMEID	INT,SCHEMENAME	VARCHAR(32),ASSISTANT	VARCHAR(32),SCHEMEACTUARY INT)
INSERT INTO @T SELECT 
1,1,'SCHEME 1','JOHN.DOE',		0 UNION ALL SELECT
1,1,'SCHEME 1','JO.BLOGS',		1 UNION ALL SELECT
1,1,'SCHEME 1','MICHAEL.SMITH',	0 UNION ALL SELECT
1,1,'SCHEME 1','JOHN.WILLIAMS',	0 UNION ALL SELECT
1,1,'SCHEME 1','MARK.WHITE',	0 UNION ALL SELECT
1,1,'SCHEME 1','RICHARD.PURPLE',0 UNION ALL SELECT
3,3,'SCHEME 2','JULIE.GREEN',	0 UNION ALL SELECT
3,3,'SCHEME 2','DAWN.GOLD',		1 UNION ALL SELECT
3,4,'SCHEME 3','ROBERT.BROWN',	0 UNION ALL SELECT
3,4,'SCHEME 3','WILLIAM.RED',	1 UNION ALL SELECT
3,4,'SCHEME 3','ALF.BLACK',		0 UNION ALL SELECT
3,4,'SCHEME 3','MARCUS.DAMSON',	0 UNION ALL SELECT
3,4,'SCHEME 3','JULIE.GREEN',	0 UNION ALL SELECT
3,5,'SCHEME 4','BILL.SMITH',	0 UNION ALL SELECT
3,5,'SCHEME 4','JOHN.THOMAS',	0 UNION ALL SELECT
3,5,'SCHEME 4','ARCHIE.HILL',	0 UNION ALL SELECT
4,6,'SCHEME 5','ADAM.JORDAN',	0 UNION ALL SELECT
4,6,'SCHEME 5','SUSAN.SMITH',	0 UNION ALL SELECT
4,6,'SCHEME 5','MICHAEL.YELLOW',1 UNION ALL SELECT
4,6,'SCHEME 5','ALBERT.ADAMS',	0

SELECT COMPANYID,SCHEMEID,SCHEMENAME,CASE WHEN SCHEMEACTUARY = 1 THEN ASSISTANT  END SCHEMEACTUARY,
		ASSISTANT,ROW_NUMBER() OVER( PARTITION BY COMPANYID,SCHEMEID ORDER BY SCHEMENAME) AS RID 
INTO #DETAILS FROM @T --WHERE SCHEMEACTUARY <> 1

DECLARE @STRCOLS VARCHAR(MAX),@VALUES VARCHAR(MAX),@STRING VARCHAR(MAX)
SELECT @STRCOLS = '',@VALUES = '' ,@STRING = ''

SELECT @STRCOLS = @STRCOLS +', MAX([' + CAST(RID AS VARCHAR(32)) + ']) AS "ASSISTANT i'+ CAST(RID AS VARCHAR(32))+'"',
		@VALUES = @VALUES + ', ['+ CAST(RID AS VARCHAR(32)) +']'
FROM
	( SELECT DISTINCT RID FROM #DETAILS)T

SELECT @STRING = '
	SELECT 
		COMPANYID,SCHEMEID,SCHEMENAME' + @STRCOLS+'			
	FROM #DETAILS
	PIVOT(MAX(ASSISTANT) FOR RID IN (' + STUFF(@VALUES,1,1,'') + '))P
	GROUP BY SCHEMEID,SCHEMENAME,COMPANYID'
	
EXEC (@STRING)

MSwanston
Starting Member

23 Posts

Posted - 2009-08-11 : 10:27:12

Ok, so have, for testing purposes, set up a temporary table of data, using the following 'Create' script:


CREATE TABLE [dbo].[TempTable](
	[CompanyID] [int] NULL,
	[SchemeID] [int] NULL,
	[SchemeName] [varchar](120) NULL,
	[Assistant] [varchar](50) NULL,
	[SchemeActuary] [bit] NULL DEFAULT ((0)))

I have then loaded the data as follows:


CompanyID      SchemeID SchemeName      Assistant       SchemeActuary
1	       1	Scheme 1	John.Doe	False
1	       1	Scheme 1	Michael.Smith	False
1	       1	Scheme 1	Dawn.Gold	True
1	       1	Scheme 1	Robert.Brown	False
1	       1	Scheme 1	Alf.Black	False
1	       1	Scheme 1	Julie.Green	False
1	       1	Scheme 1	Marcus.Damson	False
3	       3	Scheme 2	Julie.Green	False
3	       3	Scheme 2	Bill.Smith	False
3	       3	Scheme 2	William.Red	True
3	       3	Scheme 2	John.Thomas	False
3	       3	Scheme 2	Archie.Hill	False
3	       3	Scheme 2	Adam.Jordan	False
3	       4	Scheme 3	Julie.Green	False
3	       4	Scheme 3	William.Red	True
3	       4	Scheme 3	John.Thomas	False
3	       4	Scheme 3	Archie.Hill	False
3	       4	Scheme 3	Adam.Jordan	False
3	       5	Scheme 4	Julie.Green	False
3	       5	Scheme 4	William.Red	True
3	       5	Scheme 4	John.Thomas	False
3	       5	Scheme 4	Archie.Hill	False
3	       5	Scheme 4	Adam.Jordan	False
4	       6	Scheme 5	Susan.Smith	False
4	       6	Scheme 5	Michael.Yellow	True
4	       6	Scheme 5	Paul.Nixon	False
4	       6	Scheme 5	Charlie.Brown	False
4	       6	Scheme 5	Alf.Black	False
4	       6	Scheme 5	Albert.Adams	False

If I modify the code above to use this table instead, I get the almost correct output, but it does not show the SchemeActuary column, just correctly converts each scheme so they have the assistants from 1 to a maximum of 6.
If I then run the replace the data in TempTable with the data from my original query, I get 1,391 records. Using this table instead of @T in your code, it runs, but outputs the data in a very peculiar order:


CompanyID	SchemeID	SchemeName	Assistant 9	Assistant 15	Assistant 3	Assistant 12	Assistant 6	Assistant 7	Assistant 1	Assistant 18	Assistant 10	Assistant 4	Assistant 19	Assistant 13	Assistant 5	Assistant 16	Assistant 2	Assistant 17	Assistant 11	Assistant 14	Assistant 18
1	        1	        Scheme 1	NULL	        NULL	        Dawn.Gold	NULL	        Julie.Green     Marcus.Damson	John.Doe	NULL	        NULL	        Robert.Brown	NULL	        NULL	        Alf.Black	NULL            Michael.Smith	NULL	        NULL	        NULL	        NULL
3	        3	        Scheme 2	NULL	        NULL	        William.Red	NULL	        Adam.Jordan     NULL	        Julie.Green	NULL	        NULL	        John.Thomas	NULL	        NULL	        Archie.Hill	NULL	        Bill.Smith	NULL	        NULL	        NULL	        NULL
3	        4	        Scheme 3	NULL	        NULL	        John.Thomas	NULL	        NULL	        NULL	        Julie.Green	NULL	        NULL	        Archie.Hill	NULL	        NULL	        Adam.Jordan	NULL	        William.Red	NULL	        NULL	        NULL	        NULL
3	        5	        Scheme 4	NULL	        NULL	        John.Thomas	NULL	        NULL	        NULL	        Julie.Green	NULL	        NULL	        Archie.Hill	NULL	        NULL	        Adam.Jordan	NULL	        William.Red	NULL	        NULL	        NULL	        NULL
4	        6	        Scheme 5	NULL	        NULL	        Paul.Nixon	NULL	        Albert.Adams	NULL	        Susan.Smith	NULL	        NULL	        Paul.Savage	NULL	        NULL	        Alf.Black	NULL	        Michael.Yellow	NULL	        NULL	        NULL	        NULL

So, again, no SchemeActuary, and the order of the assistants is somewhat weird.
Thanks again for all your help so far.
Martin

MSwanston
Starting Member

23 Posts

Posted - 2009-11-04 : 12:45:13

OK, I have had to revisit this and have spent the afternoon trying to get this working, and although the Assistants appear correctly, I'd like, if present, the first column to be the SchemeActuary.
The code to create the temp data is as follows:


DROP TABLE SchemeAssistantsTemp

CREATE TABLE [dbo].[SchemeAssistantsTemp](
	[CompanyID] [int] NULL,
	[SchemeID] [int] NULL,
	[SchemeName] [varchar](120) NULL,
	[Assistant] [varchar](50) NULL,
	[SchemeActuary] [bit] NULL DEFAULT ((0)))

/* Populate table */
INSERT INTO SchemeAssistantsTemp ( CompanyID, SchemeID, SchemeName, Assistant, SchemeActuary ) 
VALUES( 1, 1, 'Scheme 1', 'John.Doe', 0)
INSERT INTO SchemeAssistantsTemp ( CompanyID, SchemeID, SchemeName, Assistant, SchemeActuary ) 
VALUES( 1, 1, 'Scheme 1', 'Michael.Smith', 0)
INSERT INTO SchemeAssistantsTemp ( CompanyID, SchemeID, SchemeName, Assistant, SchemeActuary ) 
VALUES( 1, 1, 'Scheme 1', 'Dawn.Gold', 1)
INSERT INTO SchemeAssistantsTemp ( CompanyID, SchemeID, SchemeName, Assistant, SchemeActuary ) 
VALUES( 1, 1, 'Scheme 1', 'Robert.Brown', 0)
INSERT INTO SchemeAssistantsTemp ( CompanyID, SchemeID, SchemeName, Assistant, SchemeActuary ) 
VALUES( 1, 1, 'Scheme 1', 'Alf.Black', 0)
INSERT INTO SchemeAssistantsTemp ( CompanyID, SchemeID, SchemeName, Assistant, SchemeActuary ) 
VALUES( 1, 1, 'Scheme 1', 'Julie.Green', 0)
INSERT INTO SchemeAssistantsTemp ( CompanyID, SchemeID, SchemeName, Assistant, SchemeActuary ) 
VALUES( 1, 1, 'Scheme 1', 'Marcus.Damson', 0)
INSERT INTO SchemeAssistantsTemp ( CompanyID, SchemeID, SchemeName, Assistant, SchemeActuary ) 
VALUES( 3, 3, 'Scheme 2', 'Julie.Green', 0)
INSERT INTO SchemeAssistantsTemp ( CompanyID, SchemeID, SchemeName, Assistant, SchemeActuary ) 
VALUES( 3, 3, 'Scheme 2', 'Bill.Smith', 0)
INSERT INTO SchemeAssistantsTemp ( CompanyID, SchemeID, SchemeName, Assistant, SchemeActuary ) 
VALUES( 3, 3, 'Scheme 2', 'William.Red', 1)
INSERT INTO SchemeAssistantsTemp ( CompanyID, SchemeID, SchemeName, Assistant, SchemeActuary ) 
VALUES( 3, 3, 'Scheme 2', 'John.Thomas', 0)
INSERT INTO SchemeAssistantsTemp ( CompanyID, SchemeID, SchemeName, Assistant, SchemeActuary ) 
VALUES( 3, 3, 'Scheme 2', 'Archie.Hill', 0)
INSERT INTO SchemeAssistantsTemp ( CompanyID, SchemeID, SchemeName, Assistant, SchemeActuary ) 
VALUES( 3, 3, 'Scheme 2', 'Adam.Jordan', 0)
INSERT INTO SchemeAssistantsTemp ( CompanyID, SchemeID, SchemeName, Assistant, SchemeActuary ) 
VALUES( 3, 4, 'Scheme 3', 'Julie.Green', 0)
INSERT INTO SchemeAssistantsTemp ( CompanyID, SchemeID, SchemeName, Assistant, SchemeActuary ) 
VALUES( 3, 4, 'Scheme 3', 'William.Red', 1)
INSERT INTO SchemeAssistantsTemp ( CompanyID, SchemeID, SchemeName, Assistant, SchemeActuary ) 
VALUES( 3, 4, 'Scheme 3', 'John.Thomas', 0)
INSERT INTO SchemeAssistantsTemp ( CompanyID, SchemeID, SchemeName, Assistant, SchemeActuary ) 
VALUES( 3, 4, 'Scheme 3', 'Archie.Hill', 0)
INSERT INTO SchemeAssistantsTemp ( CompanyID, SchemeID, SchemeName, Assistant, SchemeActuary ) 
VALUES( 3, 4, 'Scheme 3', 'Adam.Jordan', 0)
INSERT INTO SchemeAssistantsTemp ( CompanyID, SchemeID, SchemeName, Assistant, SchemeActuary ) 
VALUES( 3, 5, 'Scheme 4', 'Julie.Green', 0)
INSERT INTO SchemeAssistantsTemp ( CompanyID, SchemeID, SchemeName, Assistant, SchemeActuary ) 
VALUES( 3, 5, 'Scheme 4', 'William.Red', 1)
INSERT INTO SchemeAssistantsTemp ( CompanyID, SchemeID, SchemeName, Assistant, SchemeActuary ) 
VALUES( 3, 5, 'Scheme 4', 'Adam.Jordan', 0)
INSERT INTO SchemeAssistantsTemp ( CompanyID, SchemeID, SchemeName, Assistant, SchemeActuary ) 
VALUES( 3, 5, 'Scheme 4', 'John.Thomas', 0)
INSERT INTO SchemeAssistantsTemp ( CompanyID, SchemeID, SchemeName, Assistant, SchemeActuary ) 
VALUES( 4, 6, 'Scheme 5', 'Susan.Smith', 0)
INSERT INTO SchemeAssistantsTemp ( CompanyID, SchemeID, SchemeName, Assistant, SchemeActuary ) 
VALUES( 4, 6, 'Scheme 5', 'Michael.Yellow', 1)
INSERT INTO SchemeAssistantsTemp ( CompanyID, SchemeID, SchemeName, Assistant, SchemeActuary ) 
VALUES( 4, 6, 'Scheme 5', 'Paul.Nixon', 0)
INSERT INTO SchemeAssistantsTemp ( CompanyID, SchemeID, SchemeName, Assistant, SchemeActuary ) 
VALUES( 4, 6, 'Scheme 5', 'Charlie.Brown', 0)
INSERT INTO SchemeAssistantsTemp ( CompanyID, SchemeID, SchemeName, Assistant, SchemeActuary ) 
VALUES( 4, 6, 'Scheme 5', 'Alf.Black', 0)
INSERT INTO SchemeAssistantsTemp ( CompanyID, SchemeID, SchemeName, Assistant, SchemeActuary ) 
VALUES( 4, 6, 'Scheme 5', 'Albert.Adams', 0)

And the updated code to produce the transposed data is:


SELECT CompanyID, SchemeID, SchemeName, 
CASE WHEN SchemeActuary = 1 
	THEN Assistant 
END SchemeActuary,
Assistant, ROW_NUMBER() OVER(PARTITION BY CompanyID, SchemeID ORDER BY SchemeName) AS RID
INTO #DETAILS FROM SchemeAssistantsTemp

DECLARE @STRCOLS VARCHAR(MAX), @VALUES VARCHAR(MAX), @STRING VARCHAR(MAX)
SELECT @STRCOLS = '', @VALUES = '' ,@STRING = ''

SELECT @STRCOLS = @STRCOLS + ', MAX([' + CAST(RID AS VARCHAR(32)) + ']) AS "Assistant ' + CAST(RID AS VARCHAR(32))+ '"',
		@VALUES = @VALUES + ', [' + CAST(RID AS VARCHAR(32)) + ']'
FROM (
SELECT DISTINCT RID FROM #DETAILS) SchemeAssistantsTemp
SELECT @STRING = 
	'SELECT CompanyID, SchemeID, SchemeName' + @STRCOLS + 			
	'FROM #DETAILS PIVOT(MAX(Assistant) FOR RID IN (' + 
	STUFF(@VALUES, 1, 1, '') + ')) P
	GROUP BY SchemeID, SchemeName, CompanyID'

EXEC (@STRING)

DROP TABLE #DETAILS

It does seem to produce what I need but doesn't seem to differentiate for the SchemeActuary column, so for example the final Scheme, Michael.Yellow is SchemeActuary, but is not shown that way.
I'm hopin git is a small change to get this to work, but any help/advice gratefully received.
Regards
Martin

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources