November 17, 2016 at 5:52 am
Hi Friends ,
-- Value insertion for table :
select * into ##Test from (
select * from (values (15228683,'20 Houses',100,'Client','Omagh Integrated Primary School','Shellie Butler'),
(15228683,'20 Houses',100,'Client','Omagh Integrated Primary School','Jane Benton'),
(15160840,'Supermarket',100,'Client','OakleeTrinity Housing Association','Shellie Butler')
)X(ProjectID,Project_Name,RoleExternalID,RoleName,Officename,contact_name)
)a
select * into ##Test1 from (
select * from (values (15160840,'Supermarket',100,'Client','OakleeTrinity Housing Association','Shellie Butler',null,null,null,null,null),
(15228683,'20 Houses',100,'Client','Omagh Integrated Primary School','Shellie Butler','20 Houses',100,'Client','Omagh Integrated Primary School','Jane Benton')
)X(ProjectID,ProjectName,RoleExternalID,RoleName,Officename,contactname,Project_Name,Role_ExternalID,Role_Name,Office_name,contact_name))a
My in put is :
Select * from ##Test
expected output :
select ProjectID,ProjectName,RoleExternalID,RoleName,Officename,contactname,Project_Name,Role_ExternalID as RoleExternalID,Role_Name as RoleName,Office_name as Officename,contact_name as contactname from ##Test1
i want to do this dynamicaly depends on project_id please some one help me.
November 17, 2016 at 8:15 am
Dynamic cross-tab queries are dealt with expertly and in great detail in these two Jeff Moden articles:
November 17, 2016 at 11:17 pm
Anandkumar-SQL_Developer (11/17/2016)
Hi Friends ,-- Value insertion for table :
select * into ##Test from (
select * from (values (15228683,'20 Houses',100,'Client','Omagh Integrated Primary School','Shellie Butler'),
(15228683,'20 Houses',100,'Client','Omagh Integrated Primary School','Jane Benton'),
(15160840,'Supermarket',100,'Client','OakleeTrinity Housing Association','Shellie Butler')
)X(ProjectID,Project_Name,RoleExternalID,RoleName,Officename,contact_name)
)a
select * into ##Test1 from (
select * from (values (15160840,'Supermarket',100,'Client','OakleeTrinity Housing Association','Shellie Butler',null,null,null,null,null),
(15228683,'20 Houses',100,'Client','Omagh Integrated Primary School','Shellie Butler','20 Houses',100,'Client','Omagh Integrated Primary School','Jane Benton')
)X(ProjectID,ProjectName,RoleExternalID,RoleName,Officename,contactname,Project_Name,Role_ExternalID,Role_Name,Office_name,contact_name))a
My in put is :
Select * from ##Test
expected output :
select ProjectID,ProjectName,RoleExternalID,RoleName,Officename,contactname,Project_Name,Role_ExternalID as RoleExternalID,Role_Name as RoleName,Office_name as Officename,contact_name as contactname from ##Test1
i want to do this dynamicaly depends on project_id please some one help me.
My first suggestion is that you really need to get control over your code format. 😉
Here's how to solve your problem using a CROSSTAB. The R and C calculations I did negate the need for "dynamic" SQL IF you only ever intend to have two sets of columns. This will handle more than 2 rows per project with the understanding that there will be two rows per row in the output.
WITH
cteRows AS
(
SELECT N = ROW_NUMBER() OVER (PARTITION BY ProjectID ORDER BY ProjectID)-1
,*
FROM ##Test
)
,
cteCols AS
(
SELECT R = N / 2 --Each row will have two entries
, C = N % 2 --The entries on each row consist of two groups of columns
, *
FROM cteRows
)
SELECT ProjectID
,ProjectName = MAX(CASE WHEN C = 0 THEN Project_Name ELSE '' END)
,RoleExternalID = MAX(CASE WHEN C = 0 THEN RoleExternalID ELSE '' END)
,RoleName = MAX(CASE WHEN C = 0 THEN RoleName ELSE '' END)
,Officename = MAX(CASE WHEN C = 0 THEN Officename ELSE '' END)
,contactname = MAX(CASE WHEN C = 0 THEN contact_name ELSE '' END)
,Project_Name = MAX(CASE WHEN C = 1 THEN Project_Name ELSE '' END)
,Role_ExternalID= MAX(CASE WHEN C = 1 THEN RoleExternalID ELSE '' END)
,Role_Name = MAX(CASE WHEN C = 1 THEN RoleName ELSE '' END)
,Office_name = MAX(CASE WHEN C = 1 THEN Officename ELSE '' END)
,contact_name = MAX(CASE WHEN C = 1 THEN contact_name ELSE '' END)
FROM cteCols
GROUP BY ProjectID,R
;
Personally, I wouldn't do this. It's just going to muck up the works for any counts or other aggregates.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 18, 2016 at 6:37 am
Dear SSC-Forever ,
Thank you very much. its working well.
November 18, 2016 at 6:58 am
Anandkumar-SQL_Developer (11/18/2016)
Dear SSC-Forever ,Thank you very much. its working well.
You're welcome and thank you for the feedback. See the two article links that DesNorton posted above for how they work because you're also the one that will need to support the code if changes are requested.
As a bit of a side-bar, "SSC-Forever" is a "rank" they assign us based on our number of posts. Just above that is my avatar and just above that is my name... Jeff Moden.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply