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.
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.
JoshGoogle 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|||Answer here: http://forums.devshed.com/showthread.php?p=2022538&mode=linear#post2022538
SELECT AcctNbr,
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 TableNm
WHERE AcctNbr IN (SELECT AcctNbr FROM TableNm GROUP BY AcctNbr HAVING
COUNT(DISTINCT(Type+CodeValue))>1)
GROUP BY AcctNbr
On Apr 16, 4:26 pm, cypherus <fbsd...@.gmail.com> wrote:
> 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