add a field in the 2nd table in the pivot group.

Question

add a field in the 2nd table in the pivot group.

m_belhocine

SSC Journeyman

Points: 90
More actions
October 4, 2009 at 4:13 pm

#135547

My query
declare @r1 as varchar(4000)
declare @listcol as varchar(4000)
declare @query as varchar(4000)
SELECT @listcol = stuff((select distinct '],['+ltrim(str(campagne)) FROM (PAC2010 LEFT JOIN a_code_gestionnaire ON PAC2010.id_distributeur = a_code_gestionnaire.CODE_GESTIONNAIRE) LEFT JOIN Insp_Dist ON a_code_gestionnaire.CODE_AGENCE = Insp_Dist.Id_Dist
WHERE (((Insp_Dist.Id_Insp)='112')) order by '],['+ltrim(str(campagne)) for xml path('')),1,2,'')+']'
set @query =
'SELECT * FROM (
SELECT * FROM
(SELECT dbo.PAC2010.id_distributeur, dbo.PAC2010.Campagne, dbo.PAC2010.code_siret, dbo.a_distributeurs.Distributeur, dbo.pac_.segment, dbo.pac_.inscrit,
dbo.PAC2010.CODE_GESTIONNAIRE, dbo.a_Clubs.derniere_vue
FROM dbo.a_Clubs LEFT JOIN
dbo.a_distributeurs ON dbo.a_Clubs.Id_Dist = dbo.a_distributeurs.Id_Distributeur RIGHT JOIN
dbo.PAC2010 ON dbo.a_distributeurs.Id_Distributeur = dbo.PAC2010.id_distributeur RIGHT JOIN
dbo.pac_ ON dbo.pac_.code_gestionnaire = dbo.PAC2010.CODE_GESTIONNAIRE LEFT JOIN
dbo.Insp_Dist ON dbo.a_distributeurs.Id_Distributeur = dbo.Insp_Dist.Id_Dist
WHERE (dbo.Insp_Dist.Id_Insp = 112)
GROUP BY dbo.PAC2010.id_distributeur, dbo.PAC2010.Campagne, dbo.PAC2010.code_siret, dbo.a_distributeurs.Distributeur, dbo.pac_.segment,
dbo.pac_.inscrit, dbo.PAC2010.CODE_GESTIONNAIRE, dbo.a_Clubs.derniere_vue
) src
PIVOT (count(code_siret) FOR campagne
IN ('+@listcol+')) AS pvt
) A '
--print @query
execute (@query)
---------------------------------------------------------------------------------------------
I would add a field in the 2nd table in the pivot group.
Pvt1. Fld1 Pvt2. Fld2 ... ... ..
my tables ...
CREATE TABLE [dbo].[pac_](
[code_gestionnaire] [bigint] NULL,
[campagne] [varchar](8) COLLATE French_CI_AS NULL,
[segment] [char](10) COLLATE French_CI_AS NULL,
[inscrit] [nchar](10) COLLATE French_CI_AS NULL,
[date_debut] [nchar](10) COLLATE French_CI_AS NULL,
[date_fin] [nchar](10) COLLATE French_CI_AS NULL,
[regles] [nchar](10) COLLATE French_CI_AS NULL,
[flag1] [nchar](10) COLLATE French_CI_AS NULL,
[flag2] [nchar](10) COLLATE French_CI_AS NULL,
[flag3] [nchar](10) COLLATE French_CI_AS NULL,
[reserver] [nchar](10) COLLATE French_CI_AS NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[PAC2010](
[code_siret] [varchar](14) COLLATE French_CI_AS NULL,
[id_distributeur] [bigint] NULL,
[CODE_GESTIONNAIRE] [bigint] NULL,
[Campagne] [varchar](8) COLLATE French_CI_AS NULL,
[produit1] [varchar](20) COLLATE French_CI_AS NULL,
[produit2] [varchar](20) COLLATE French_CI_AS NULL,
[produit3] [varchar](20) COLLATE French_CI_AS NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[a_Clubs](
[Id_Dist] [bigint] NULL,
[Club0] [smallint] NULL,
[Club1] [smallint] NULL,
[Club2] [smallint] NULL,
[derniere_vue] [nvarchar](50) COLLATE French_CI_AS NULL,
[2005] [nvarchar](50) COLLATE French_CI_AS NULL,
[2006] [nvarchar](50) COLLATE French_CI_AS NULL,
[2007] [nvarchar](50) COLLATE French_CI_AS NULL,
[2008] [nvarchar](50) COLLATE French_CI_AS NULL,
[Ecole] [datetime] NULL,
[Universite] [datetime] NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[Insp_Dist](
[Id_Insp] [bigint] NULL,
[Id_Dist] [bigint] NULL,
) ON [PRIMARY]
CREATE TABLE [dbo].[campagne](
[C_ID] [varchar](50) COLLATE French_CI_AS NULL,
[C_Nom] [nvarchar](25) COLLATE French_CI_AS NULL,
[C_DateDebut] [datetime] NULL,
[C_DateFin] [datetime] NULL,
[C_CampagnesInterdites] [nvarchar](25) COLLATE French_CI_AS NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[a_distributeurs](
[Id_Distributeur] [bigint] NOT NULL,
[Type_Distrib] [smallint] NULL,
[distributeur_prenom] [nvarchar](15) COLLATE French_CI_AS NULL,
[Distributeur] [nvarchar](30) COLLATE French_CI_AS NULL,
) ON [PRIMARY]
CREATE TABLE [dbo].[a_Clubs](
[Id_Dist] [bigint] NULL,
[derniere_vue] [nvarchar](50) COLLATE French_CI_AS NULL,
) ON [PRIMARY]
PEASE HELP ME URGENT

Viewing 3 posts - 1 through 3 (of 3 total)

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

You might want to have a look at Jeff Moden's article regarding Dynamic Cross Tabs [/url].

If you need further assistance please provide sample data as described in the link in my signature. This will help us help you.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

m_belhocine SSC Journeyman Points: 90 More actions · Answer 2

m_belhocine

SSC Journeyman

Points: 90

October 5, 2009 at 4:45 am

#1061630

thanks