Pivot Query?

I have a table that's in the format:
ClientCode Form1 Form2 Form3
---
ABC 500 750 800
...
...
And I'm looking to rotate it so that I have the following fields:
ClientCode, FormType, Amount. However, the table currently has a lot of
data that will need to be moved to the new format. Ultimately I want to end
up with:
ClientCode FormType Amount
---
ABC Form1 500
ABC Form2 750
ABC Form3 800
I know there's a way to do this but I can't recall off the top of my head
the simplest way. Anyone have some direction for me? Thanks!Try this:
INSERT INTO NewTable (clientcode, formtype, amount)
SELECT clientcode, 'Form1', form1
FROM YourTable
WHERE form1 IS NOT NULL
UNION ALL
SELECT clientcode, 'Form2', form2
FROM YourTable
WHERE form2 IS NOT NULL
UNION ALL
SELECT clientcode, 'Form3', form3
FROM YourTable
WHERE form3 IS NOT NULL
--
David Portas
SQL Server MVP
--|||Exactly what I was looking for, thanks.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:k_idnTjk9PpqlpjcRVn-qQ@.giganews.com...
> Try this:
> INSERT INTO NewTable (clientcode, formtype, amount)
> SELECT clientcode, 'Form1', form1
> FROM YourTable
> WHERE form1 IS NOT NULL
> UNION ALL
> SELECT clientcode, 'Form2', form2
> FROM YourTable
> WHERE form2 IS NOT NULL
> UNION ALL
> SELECT clientcode, 'Form3', form3
> FROM YourTable
> WHERE form3 IS NOT NULL
> --
> David Portas
> SQL Server MVP
> --
>