Pull data from rows to make columns

I have a table with three columns:
AcctNbr, Type, CodeValue
Listed below is an example of the database.
AcctNbrTypeCodeValue
1MAILCODE99
2MAILCODE99
3MAILCODE99
4MAILCODE90
4MAILCODE99
4SEG1 O
5MAILCODE99
6MAILCODE99
7MAILCODE99
8MAILCODE99
9MAILCODE99
10MAILCODE90
11MAILCODE99
12MAILCODE99
13MAILCODE99
14MAILCODE99
15LIST DS1
15MAILCODE99
There are multiple Type's for some AcctNbr's and what I want to do is
run a query on the database so that if the AcctNbr has multiple Type's
and CodesValue's it takes them and creates new columns like so:
AcctNbr MailCode_90 MailCode_99 SEG1
4 90 99 O
So on and so forth. There are multiple Type's and multiple codes that
I need to do this with for each account number. If someone could give
me a base code to try I could start somewhere. I am an SQL novice.
Thanks.
Josh
Google for pivot in t-sql
MC
"cypherus" <fbsdguy@.gmail.com> wrote in message
news:9bc69bb7-1996-4d07-a0c2-fdace73e4c4f@.y21g2000hsf.googlegroups.com...
>I have a table with three columns:
> AcctNbr, Type, CodeValue
> Listed below is an example of the database.
> AcctNbr Type CodeValue
> 1 MAILCODE 99
> 2 MAILCODE 99
> 3 MAILCODE 99
> 4 MAILCODE 90
> 4 MAILCODE 99
> 4 SEG1 O
> 5 MAILCODE 99
> 6 MAILCODE 99
> 7 MAILCODE 99
> 8 MAILCODE 99
> 9 MAILCODE 99
> 10 MAILCODE 90
> 11 MAILCODE 99
> 12 MAILCODE 99
> 13 MAILCODE 99
> 14 MAILCODE 99
> 15 LIST DS1
> 15 MAILCODE 99
> There are multiple Type's for some AcctNbr's and what I want to do is
> run a query on the database so that if the AcctNbr has multiple Type's
> and CodesValue's it takes them and creates new columns like so:
> AcctNbr MailCode_90 MailCode_99 SEG1
> 4 90 99 O
> So on and so forth. There are multiple Type's and multiple codes that
> I need to do this with for each account number. If someone could give
> me a base code to try I could start somewhere. I am an SQL novice.
> Thanks.
> Josh
|||Your best bet is to do this with the Rac utility. It will minimize the ugly
sql coding necessary. If your interested post back and I'll hook you up with
the Rac execute statement for this.
www.rac4sql.net
www.beyondsql.blogspot.com
|||On Apr 10, 5:56 am, "steve dassin" <stevenos...@.rac4sql.net> wrote:
> Your best bet is to do this with the Rac utility. It will minimize the ugly
> sql coding necessary. If your interested post back and I'll hook you up with
> the Rac execute statement for this.www.rac4sql.net
> www.beyondsql.blogspot.com
I don't really want to download any new programs just for that. I did
post to a couple other boards and somebody gave me some test code that
throws up errors. Can someone either help me fix the errors or help
me with some new test code? Here's the code that I was given:
SELECT AcctNbr FROM [KMAi_V8].[dbo].[A04CODE],
MAX(CASE WHEN Type='MAILCODE' AND CodeValue='90' THEN CodeValue ELSE
NULL END) MailCode_90,
MAX(CASE WHEN Type='MAILCODE' AND CodeValue='99' THEN CodeValue ELSE
NULL END) MailCode_99,
MAX(CASE WHEN Type='SEG1' AND CodeValue='O' THEN Type ELSE NULL END)
SEG1
FROM Acct
WHERE AcctNbr IN (SELECT AcctNbr FROM Acct GROUP BY AcctNbr HAVING
COUNT(DISTINCT(Type+CodeValue))>1)
GROUP BY AcctNbr
And it tosses up this error:
TITLE: SQL Server Import and Export Wizard
The statement could not be parsed.
ADDITIONAL INFORMATION:
Deferred prepare could not be completed.
Statement(s) could not be prepared.
Incorrect syntax near the keyword 'GROUP'.
Incorrect syntax near the keyword 'CASE'. (Microsoft SQL Native
Client)
BUTTONS:
OK
Any ideas?
Josh