building columns - 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

building columns

Author

Topic

detlion1643
Yak Posting Veteran

67 Posts

Posted - 2010-02-13 : 09:43:29

I have a table that is imported from excel. I was hoping there was a way to build out 5 different columns with summed values.
Excel imported file:
Part , From , Dol , ExpDate
aaa , All , 25 , 2/28/2010
aaa , 1 , 10 , 2/28/2010
bbb , 2 , 5 , 2/28/2010

The ones from All must be summed into all 5 columns, but the ones from 1-5 must be summed into the appropriate column.
Like this:
Part , 1 , 2 , 3 , 4 , 5
aaa , 35 , 25 , 25 , 25 , 25
bbb , , 5 , , ,

Hope this makes sense and might be able to be accomplished.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-13 : 09:55:34

make use of OPENROWSET

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

detlion1643
Yak Posting Veteran

67 Posts

Posted - 2010-02-13 : 11:00:20

I'm not sure if I understand OPENROWSET correctly, but I already have the information in the table. I am looking to write a query that pulls the data into 5 columns based on whatever is in the [From] field and adding to it if the [Part] already exists...
Is OPENROWSET an alternative to pulling the information into a table (query to get the info instead of importing to a table?)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-13 : 11:04:19

Oh ok..so you've the table. then its just a matter of


SELECT Part,
SUM(CASE WHEN From = 1 OR From = 'All' THEN Dol ELSE 0 END) AS [1],
SUM(CASE WHEN From = 2 OR From = 'All' THEN Dol ELSE 0 END) AS [2],
...
SUM(CASE WHEN From = 5 OR From = 'All' THEN Dol ELSE 0 END) AS [5]
FROM Table
GROUP BY Part

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

detlion1643
Yak Posting Veteran

67 Posts

Posted - 2010-02-13 : 11:06:21

That makes sense, duh!
I was thinking I had to write a udf to check everything...
Thanks for the simple solution!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-13 : 11:09:01

welcome