Pivot query ? - 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

General SQL Server Forums

New to SQL Server Programming

Pivot query ?

Author

Topic

Corobori
Posting Yak Master

105 Posts

Posted - 2010-01-19 : 14:37:08

My query


SELECT     dbo.tblBienestarSolicitudesDetalles.Bsd_Bd_Id, dbo.tblBienestarSolicitudesDetalles.Bsd_Fod_Id, 
                      dbo.tblBienestarSolicitudesDetalles.Bsd_Texto
FROM         dbo.tblFormulariosDetalles INNER JOIN
                      dbo.tblBienestarSolicitudesDetalles ON dbo.tblFormulariosDetalles.Fod_No = dbo.tblBienestarSolicitudesDetalles.Bsd_Fod_Id
WHERE     (dbo.tblFormulariosDetalles.Fod_Fo_Id = 1)

gives me a "vertical" result


13	1	CONTRATO
13	2	SALUD
13	3	PREVISION
13	4	PERTENECE 
13	5	DOMICILIO
13	6	COMUNA
13	7	CIUDAD
13	8	TELEFONO
13	9	REGION
13	10	MI

I want to have it this way


13, CONTRATO, SALUD, PREVISION, PERTENECE, DOMICILIO, COMUNA, CIUDAD, TELEFONO, REGION, MI

I tried, for the 1st time, the PIVOT feature, but it isn't working


SELECT     Bsd_Texto
FROM         dbo.tblFormulariosDetalles INNER JOIN
                      dbo.tblBienestarSolicitudesDetalles ON dbo.tblFormulariosDetalles.Fod_No = dbo.tblBienestarSolicitudesDetalles.Bsd_Fod_Id

PIVOT
(
max(Bsd_Texto)

For Fod_Fo_Id in ([1],[2],[3],[4],[5],[6],[7],,[9],[10]))

as sTexto

WHERE     (Fod_Fo_Id = 1)

My tables are



SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblFormulariosDetalles](
	[Fod_Id] [int] IDENTITY(1,1) NOT NULL,
	[Fod_No] [int] NOT NULL,
	[Fod_Fo_Id] [int] NOT NULL,
	[Fod_Texto] [nvarchar](250) COLLATE Latin1_General_CI_AS NOT NULL,
 CONSTRAINT [PK_tblFormulariosDetalles] PRIMARY KEY CLUSTERED 
(
	[Fod_Id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblBienestarSolicitudesDetalles](
	[Bsd_Id] [int] IDENTITY(1,1) NOT NULL,
	[Bsd_Bd_Id] [int] NOT NULL,
	[Bsd_Fod_Id] [int] NOT NULL,
	[Bsd_Texto] [nvarchar](100) COLLATE Latin1_General_CI_AS NOT NULL,
 CONSTRAINT [PK_tblBienestarSolicitudesDetalles] PRIMARY KEY CLUSTERED 
(
	[Bsd_Id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

jean-luc
www.corobori.com

sql-programmers
Posting Yak Master

190 Posts

Posted - 2010-01-20 : 04:10:22

Hi,

I have a blog article to maybe assist you with your question:

http://www.sql-programmers.com/Blog/tabid/153/EntryId/6/Using-PIVOT-and-UNPIVOT.aspx

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

divyaram
Posting Yak Master

180 Posts

Posted - 2010-01-20 : 06:06:09

SELECT Bsd_Texto
FROM dbo.tblFormulariosDetalles
INNER JOIN
dbo.tblBienestarSolicitudesDetalles ON dbo.tblFormulariosDetalles.Fod_No = dbo.tblBienestarSolicitudesDetalles.Bsd_Fod_Id
where Fod_Fo_Id in ([1],[2],[3],[4],[5],[6],[7],,[9],[10]
PIVOT
(
max(Bsd_Texto)
For Bsd_Bd_Id in[13]) as pvt
order by tblFormulariosDetalles.Fod_Fo_Id

Regards,
Divya

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-01-20 : 06:09:00

or old school:


/*
SELECT     dbo.tblBienestarSolicitudesDetalles.Bsd_Bd_Id, dbo.tblBienestarSolicitudesDetalles.Bsd_Fod_Id, 
                      dbo.tblBienestarSolicitudesDetalles.Bsd_Texto
FROM         dbo.tblFormulariosDetalles INNER JOIN
                      dbo.tblBienestarSolicitudesDetalles ON dbo.tblFormulariosDetalles.Fod_No = dbo.tblBienestarSolicitudesDetalles.Bsd_Fod_Id
WHERE     (dbo.tblFormulariosDetalles.Fod_Fo_Id = 1)


gives me a "vertical" result


13	1	CONTRATO
13	2	SALUD
13	3	PREVISION
13	4	PERTENECE 
13	5	DOMICILIO
13	6	COMUNA
13	7	CIUDAD
13	8	TELEFONO
13	9	REGION
13	10	MI 

TO RESULTS
13, CONTRATO, SALUD, PREVISION, PERTENECE, DOMICILIO, COMUNA, CIUDAD, TELEFONO, REGION, MI 

*/

SELECT
	[ID]
	, MAX(CASE WHEN [div] = 'CONTRATO' THEN [val] ELSE NULL END) AS [CONTRATO]
	, MAX(CASE WHEN [div] = 'SALUD' THEN [val] ELSE NULL END) AS [SALUD]
	, MAX(CASE WHEN [div] = 'PREVISION' THEN [val] ELSE NULL END) AS [PREVISION]
	, MAX(CASE WHEN [div] = 'PERTENECE' THEN [val] ELSE NULL END) AS [PERTENECE]
	, MAX(CASE WHEN [div] = 'DOMICILIO' THEN [val] ELSE NULL END) AS [DOMICILIO]
	, MAX(CASE WHEN [div] = 'COMUNA' THEN [val] ELSE NULL END) AS [COMUNA]
	, MAX(CASE WHEN [div] = 'CIUDAD' THEN [val] ELSE NULL END) AS [CIUDAD]
	, MAX(CASE WHEN [div] = 'TELEFONO' THEN [val] ELSE NULL END) AS [TELEFONO]
	, MAX(CASE WHEN [div] = 'REGION' THEN [val] ELSE NULL END) AS [REGION]
	, MAX(CASE WHEN [div] = 'MI' THEN [val] ELSE NULL END) AS [MI]
FROM
	(
	SELECT
		dbo.tblBienestarSolicitudesDetalles.Bsd_Bd_Id AS [ID]
		, dbo.tblBienestarSolicitudesDetalles.Bsd_Fod_Id AS [val]
		, dbo.tblBienestarSolicitudesDetalles.Bsd_Texto AS [div]
	FROM
		dbo.tblFormulariosDetalles
		INNER JOIN dbo.tblBienestarSolicitudesDetalles ON dbo.tblFormulariosDetalles.Fod_No = dbo.tblBienestarSolicitudesDetalles.Bsd_Fod_Id
	WHERE
		dbo.tblFormulariosDetalles.Fod_Fo_Id = 1
	)
	piv
GROUP BY
	[ID]

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Corobori
Posting Yak Master

105 Posts

Posted - 2010-01-20 : 07:42:47

quote:
Originally posted by divyaram

SELECT Bsd_Texto
FROM dbo.tblFormulariosDetalles
INNER JOIN
dbo.tblBienestarSolicitudesDetalles ON dbo.tblFormulariosDetalles.Fod_No = dbo.tblBienestarSolicitudesDetalles.Bsd_Fod_Id
where Fod_Fo_Id in ([1],[2],[3],[4],[5],[6],[7],,[9],[10]
PIVOT
(
max(Bsd_Texto)
For Bsd_Bd_Id in[13]) as pvt
order by tblFormulariosDetalles.Fod_Fo_Id

Regards,
Divya

I tried your way


SELECT Bsd_Texto
FROM dbo.tblFormulariosDetalles
INNER JOIN
dbo.tblBienestarSolicitudesDetalles ON dbo.tblFormulariosDetalles.Fod_No = dbo.tblBienestarSolicitudesDetalles.Bsd_Fod_Id
where Fod_Fo_Id in ([1],[2],[3],[4],[5],[6],[7],,[9],[10]
PIVOT
(
max(Bsd_Texto)
For Bsd_Bd_Id in [13] ) as pvt
order by tblFormulariosDetalles.Fod_Fo_Id

but is says

Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'PIVOT'.

Not sure where I should the "(" located after "where Fod_Fo_Id in"

jean-luc
www.corobori.com

divyaram
Posting Yak Master

180 Posts

Posted - 2010-01-20 : 08:02:26

SELECT dbo.tblBienestarSolicitudesDetalles.Bsd_Bd_Id,
dbo.tblBienestarSolicitudesDetalles.Bsd_Fod_Id,
dbo.tblBienestarSolicitudesDetalles.Bsd_Texto
FROM (select dbo.tblBienestarSolicitudesDetalles.Bsd_Texto from dbo.tblFormulariosDetalles
INNER JOIN
dbo.tblBienestarSolicitudesDetalles
ON dbo.tblFormulariosDetalles.Fod_No = dbo.tblBienestarSolicitudesDetalles.Bsd_Fod_Id
WHERE (dbo.tblFormulariosDetalles.Fod_Fo_Id = 1))as Bsd_Texto
PIVOT
(
max(Bsd_Texto)

For Fod_Fo_Id in ([1],[2],[3],[4],[5],[6],[7],,[9],[10]))

as pvt

Regards,
Divya

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources