Pivot. First time. - 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. First time.

Author

Topic

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-09-17 : 11:28:35

Alright. I've been waiting for an answer from my boss on this since Friday. i was TOLD the project i'm on was time-critical. The project depends on the answer to this issue. Four days seems an excessive wait.

Anyway, i have a table where i need to use the pivot function to create a new table from the columns / rows specified. I used an example that was provided in a tutorial to try to extrapolate the use of PIVOT for my purposes, but i'm sure my syntax is all off.

I need to take the rows with the values:
1570, 1571, 1573, 1586, 1901, 1917, 1935, 1936
for the following columns:
CFSID, actionTaken

and pivot those. The code i'm attempting to use is as follows:



select 1570, 1571, 1573, 1586, 1901, 1917, 1935, 1936 from (Select cfsid, actionTaken From  douglasconversion.dbo.inserttoCTSDVdata)up 
pivot  inserttoCTSDVdata in (1570, 1571, 1573, 1586, 1901, 1917, 1935, 1936) as pvt

my original table looks like:



CREATE TABLE [dbo].[InsertToCTSDVdata](
	[CFSID] [int] NOT NULL,
	[ChildrenInvolved] [int] NULL,
	[ChildrenPresent] [int] NULL,
	[PreviousComplaintsID] [int] NULL,
	[PriorCourtOrders] [int] NULL,
	[VictimAdvised] [int] NULL,
	[AbuseTypeIDs] [varchar](100) NULL,
	[ActionTakenIDs] [varchar](100) NULL,
	[NoArrestIDs] [varchar](50) NULL,
	[AggressorIdentifyIDs] [varchar](50) NULL,
	[SubstanceAbuse] [int] NULL,
	[AggressorSubstanceIDs] [varchar](50) NULL,
	[VictimSubstanceIDs] [varchar](50) NULL,
	[RelationshipIDs] [varchar](100) NULL,
	[ReportDate] [datetime] NULL,
	[ApprovalOfficerID] [int] NULL,
	[Approved] [bit] NULL,
	[ApprovalDate] [datetime] NULL,
	[ReviewDate] [datetime] NULL,
	[ReviewerID] [int] NULL,
	[EntryDate] [datetime] NULL,
	[Deleted] [bit] NULL,
	[AbuseTypeDesc] [int] NULL,
	[ActionTaken] [int] NULL,
	[Relationship] [int] NULL,
	[AggressorIdentifier] [int] NULL,
	[AggressorSubstance] [int] NULL,
	[VictimSubstance] [int] NULL
) ON [PRIMARY]

GO

Help?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-17 : 11:41:12

so what should be output format?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-09-17 : 12:22:10

Hold that thought. I'm now being told by the powers that be that the pivot idea might not be what is needed for this.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-17 : 13:02:24

ok...let us know if you need more help

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-17 : 13:02:25

ok...let us know if you need more help

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-09-17 : 14:25:08

Ugh. The quality of communication in my office is pathetic.

So what i need are the following values to become the COLUMNS (Unique values in the current ActionTaken column):
1570, 1571, 1573, 1586, 1901, 1917, 1935, 1936

and the CFSID values become the rows.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-17 : 14:36:56

thats again sounding as pivotting for me

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-09-17 : 14:38:50

I agree. As i said, the communication lines here are poor. It is, in fact, a pivot i need to do, but how? I've tried from an online tutorial but am not getting very far.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-17 : 14:41:33

[code]
SELECT *
FROM table
PIVOT(MAX(CFSID) FOR ActionTaken IN ([1570],[1571],[1573],[1586],[1901],[1917],[1935],[1936]))p
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-09-17 : 14:46:26

Thanks. Testing. Will report back.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-17 : 14:48:00

ok...will wait for your feedback

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-09-17 : 14:50:18

Interesting. So that basically adds the columns 1570, 1571, etc to the existing columns in the table. In fact, what i would prefer is that i am seeing a table with ONLY those new columns. Would i then change the "Select *" to a specific "Select 1570, 1571,.... into DB.dbo.NewTable" in the same query to get that sort of resultset?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-17 : 14:53:06

yep...you can specifically select

to make it dynamic you can extend it to this too

http://beyondrelational.com/modules/2/blogs/70/posts/10840/dynamic-pivot-in-sql-server-2005.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-09-17 : 14:56:47

Thank you. that was far simpler than i expected. I don't know why everyone in my office was making it out to be such a headache.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-17 : 14:57:31

may be nobody had ever tried it so far or had no idea on dynamic pivotting

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-09-17 : 15:07:28

The pools of information around here are so fragmented, it's hard to piece together one way of doing things. I get part of a query from one person, another part from someone else, but it doesn't work with the first part. I either learn something new or start to lose my mind. Thanks again for the help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-17 : 15:20:24

you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources