Insert columns from selected rows

[imtmub]

Hi all,
here my select command

SELECT SUM(dbo.ITR.ITR _TransQty * dbo.ITR.ITR_Uni tCostAmt) from itr
where itr_transdate between '2007-12-01' and '2007-12-31'
group by itr_glacctnbr
having itr_glacctnbr=' 121101'

This statement select only one glaccount but i have more than 15 glaccounts to select.

I can use this statement to select all glaccount but i want to select specific glaccounts

SELECT sUM(dbo.ITR.ITR _TransQty * dbo.ITR.ITR_Uni tCostAmt) from itr
where itr_transdate between'2007-12-01' and '2007-12-31'
group by itr_glacctnbr


I can get result in rows that i want to inset into another table called 'testgl'

That table contains field of all different gl_accounts. fields like 121101, 121102,12103... .etc


How to convert selected rows in column and insert on this table fields.

Thanks in advance for your answer.

[Delerna]

I think I understand your question.
You want to insert more than the one row returned by the first query but not all the rows in the second ???

If so how about using more criteria in the first query
ie HAVING itr_glacctnbr=' 121101' or itr_glacctnbr=' 121105' or .......

Or maybe another table where you store the itr_glacctnbr numbers you want to insert and join it to the second query

[imtmub]

Thanks for your reply.
I have tried using having still i am not getting the result. here my statement

SELECT SUM(dbo.ITR.ITR _TransQty * dbo.ITR.ITR_Uni tCostAmt) from itr
where itr_transdate between '2007-12-01' and '2007-12-31'
group by itr_glacctnbr
having itr_glacctnbr=' 121101'or itr_glacctnbr=' 121102'


This statement give only one result for 121101. not for 121102.

If i use below statement i can get the result for 121102.

SELECT SUM(dbo.ITR.ITR _TransQty * dbo.ITR.ITR_Uni tCostAmt) from itr
where itr_transdate between '2007-12-01' and '2007-12-31'
group by itr_glacctnbr
having itr_glacctnbr=' 121102'

But i want in one statement should give both result. because i want to filter 15 different itr_glaccounts in one statement.

I have more than 100 gl_accounts in my table but i want only few.

[imtmub]

I think I understand your question.
You want to insert more than the one row returned by the first query but not all the rows in the second ???

If so how about using more criteria in the first query
ie HAVING itr_glacctnbr=' 121101' or itr_glacctnbr=' 121105' or .......

Or maybe another table where you store the itr_glacctnbr numbers you want to insert and join it to the second query

Thanks delerna,

Sorry for last reply. My table doesn't have any data with that date.

Now i have one more question.

I am getting the result like this for the select statement

45000
67786

First result for Itr_glacct 121101, 121102 respectively.


This result i need to insert in another table as one row.

Because in another table i have the fields like 121101, 121102..

First select command rows insert on the this table as single row.

121101, 121102
------------------------
45000, 67786

[Delerna]

is this a regular thing that you will need to do???
Will it be a different set of itr_glacctnbr each time
If so, may I suggest you create a table called something like tblGLAccsToIncl ude with 1 field called glacctnbr
Then you could add the GLAcc's you want to the table and change the query to
something like

SELECT SUM(dbo.ITR.ITR_TransQty * dbo.ITR.ITR_UnitCostAmt) 
from itr
join tblGLAccsToInclude on itr_glacctnbr=glacctnbr
where itr_transdate between '2007-12-01' and '2007-12-31'
group by itr_glacctnbr

if it's a one off thing then try changing it to

SELECT SUM(dbo.ITR.ITR_TransQty * dbo.ITR.ITR_UnitCostAmt) 
from itr
where itr_transdate between '2007-12-01' and '2007-12-31'
   and (itr_glacctnbr='100021' or itr_glacctnbr='100022' or itr_glacctnbr='100023' or ---)
group by itr_glacctnbr

[Delerna]

I take it that the GLAcc's are a fixed set of numbers.
Make your query a subquery by wrapping in brackets and add field itr_glacctnbr
then write a

Select sum(Case when itr_glacctnbr='121101' then Amt else 0 end) as 121101,
          sum(Case when itr_glacctnbr='121102' then Amt else 0 end) as 121102,
          ......
FROM
(   SELECT itr_glacctnbr,
    SUM(dbo.ITR.ITR_TransQty * dbo.ITR.ITR_UnitCostAmt) as Amt from itr
    where itr_transdate between '2007-12-01' and '2007-12-31'
    group by itr_glacctnbr
    having itr_glacctnbr='121101'  or  itr_glacctnbr='121102' ....
)a

[imtmub]

is this a regular thing that you will need to do???
Will it be a different set of itr_glacctnbr each time
If so, may I suggest you create a table called something like tblGLAccsToIncl ude with 1 field called glacctnbr
Then you could add the GLAcc's you want to the table and change the query to
something like

SELECT SUM(dbo.ITR.ITR_TransQty * dbo.ITR.ITR_UnitCostAmt) 
from itr
join tblGLAccsToInclude on itr_glacctnbr=glacctnbr
where itr_transdate between '2007-12-01' and '2007-12-31'
group by itr_glacctnbr

if it's a one off thing then try changing it to

SELECT SUM(dbo.ITR.ITR_TransQty * dbo.ITR.ITR_UnitCostAmt) 
from itr
where itr_transdate between '2007-12-01' and '2007-12-31'
   and (itr_glacctnbr='100021' or itr_glacctnbr='100022' or itr_glacctnbr='100023' or ---)
group by itr_glacctnbr

Thanks Once again Delerna.

Both statements working fine. now i need to insert this result into another table. that table got fields of 121101, 121102.....

So i need to convert the results from rows to coloumn. so that i can use insert statement.

[Delerna]

I take it that the GLAcc's are a fixed set of numbers.
Make your query a subquery by wrapping in brackets and add field itr_glacctnbr
then write a

Select sum(Case when itr_glacctnbr='121101' then Amt else 0 end) as 121101,
          sum(Case when itr_glacctnbr='121102' then Amt else 0 end) as 121102,
          ......
FROM
(   SELECT itr_glacctnbr,
    SUM(dbo.ITR.ITR_TransQty * dbo.ITR.ITR_UnitCostAmt) as Amt from itr
    where itr_transdate between '2007-12-01' and '2007-12-31'
    group by itr_glacctnbr
    having itr_glacctnbr='121101'  or  itr_glacctnbr='121102' ....
)a

Does that help

[imtmub]

I take it that the GLAcc's are a fixed set of numbers.
Make your query a subquery by wrapping in brackets and add field itr_glacctnbr
then write a

Select sum(Case when itr_glacctnbr='121101' then Amt else 0 end) as 121101,
          sum(Case when itr_glacctnbr='121102' then Amt else 0 end) as 121102,
          ......
FROM
(   SELECT itr_glacctnbr,
    SUM(dbo.ITR.ITR_TransQty * dbo.ITR.ITR_UnitCostAmt) as Amt from itr
    where itr_transdate between '2007-12-01' and '2007-12-31'
    group by itr_glacctnbr
    having itr_glacctnbr='121101'  or  itr_glacctnbr='121102' ....
)a

Does that help

I am getting error message like this

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '121101'.

Here is my query

Select sum(Case when itr_glacctnbr=' 121101' then Amt else 0 end) as 121101,
sum(Case when itr_glacctnbr=' 121102' then Amt else 0 end) as 121102,
sum(Case when itr_glacctnbr=' 121103' then Amt else 0 end) as 121103,
sum(Case when itr_glacctnbr=' 122101' then Amt else 0 end) as 122101,
sum(Case when itr_glacctnbr=' 124101' then Amt else 0 end) as 124101,
sum(Case when itr_glacctnbr=' 124102' then Amt else 0 end) as 124102,
sum(Case when itr_glacctnbr=' 124104' then Amt else 0 end) as 124104,
sum(Case when itr_glacctnbr=' 124105' then Amt else 0 end) as 124105,
sum(Case when itr_glacctnbr=' 124107' then Amt else 0 end) as 124107,
sum(Case when itr_glacctnbr=' 124108' then Amt else 0 end) as 124108,
sum(Case when itr_glacctnbr=' 124109' then Amt else 0 end) as 124109,
sum(Case when itr_glacctnbr=' 124301' then Amt else 0 end) as 124301,
sum(Case when itr_glacctnbr=' 124302' then Amt else 0 end) as 124302,
sum(Case when itr_glacctnbr=' 124305' then Amt else 0 end) as 124305
FROM
( SELECT itr_glacctnbr,
SUM(dbo.ITR.ITR _TransQty * dbo.ITR.ITR_Uni tCostAmt) as Amt from itr
where itr_transdate between '2007-12-01' and '2007-12-31'
group by itr_glacctnbr
Having itr_glacctnbr=' 121101'or
itr_glacctnbr=' 121102' or
itr_glacctnbr=' 121103' or
itr_glacctnbr=' 122101' or
itr_glacctnbr=' 124101' or
itr_glacctnbr=' 124102' or
itr_glacctnbr=' 124104' or
itr_glacctnbr=' 124105' or
itr_glacctnbr=' 124107' or
itr_glacctnbr=' 124108' or
itr_glacctnbr=' 124109' or
itr_glacctnbr=' 124301' or
itr_glacctnbr=' 124302' or
itr_glacctnbr=' 124305' )

[Delerna]

sorry, if you want a number as a field name it must be enclosed in [ ]
should be
Select sum(Case when itr_glacctnbr=' 121101' then Amt else 0 end) as [121101],
etc

[imtmub]

sorry, if you want a number as a field name it must be enclosed in [ ]
should be
Select sum(Case when itr_glacctnbr=' 121101' then Amt else 0 end) as [121101],
etc

Thanks a lot. It is working i have inserted the records to another table. thanks for your guidense.