February 24, 2014 at 7:28 am
I have this query which I run off off of one table that is vertical.
It turns the data horizontal.
Once I run it I want to be able to insert this data into another existing table with columns of the same name.
how would I write this? the sql is long I just have shortened it up for here
Would I use a select into or insert into? and if so how would I write that into this code?
select icn,
'ADMIT' = case when MAX(case when [sequence number] = 'a' then [diagnosis code] end) IS null
then ''
else MAX(case when [sequence number] = 'a' then [diagnosis code] end)
end
,'Seq 1' = case when MAX(case when [sequence number] = '01' then [diagnosis code] end) IS null
then ''
from dx1
group by icn
February 24, 2014 at 7:40 am
Just add
INTO SomeTableName
right before the FROM clause.
select icn,
'ADMIT' = case when MAX(case when [sequence number] = 'a' then [diagnosis code] end) IS null
then ''
else MAX(case when [sequence number] = 'a' then [diagnosis code] end)
end
,'Seq 1' = case when MAX(case when [sequence number] = '01' then [diagnosis code] end) IS null
then ''
INTO YourTableNameHere
from dx1
group by icn
If the table already exists this will throw an exception. If the table does not exist it will be created.
I am certain you could make this easier by breaking apart the nested case expressions. I have been trying to unravel the logic in there and I just can't figure out what you trying to do there. I think you could simplify to this??
select icn,
MAX(case when [sequence number] = 'a' then ISNULL([diagnosis code], '') end) as Admit
,MAX(case when [sequence number] = '01' then ISNULL([diagnosis code], '') end) as [Seq 1]
from dx1
group by icn
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 24, 2014 at 7:48 am
Sean, you had actually helped me out with the original query in another post,
I need to transform the data from one table
from
ICN|Sequence #|DX CODE
123| 01 |12345
123| 02 |456789
to
ICN| SEQ 1 |SEQ 2
123| 12345 |456789
Once I get it into this format, then I want to insert into another existing table.
I do this because of the way the data is set up and for another query I Am running
February 24, 2014 at 7:50 am
and yes I Get the error There is already an object named 'eapgdx' in the database.
If the table is not created, will it create a temp table or a permanent table?
February 24, 2014 at 7:56 am
montecarlo2079 (2/24/2014)
and yes I Get the error There is already an object named 'eapgdx' in the database.If the table is not created, will it create a temp table or a permanent table?
If the table already exists you have to move the INSERT.
INSERT INTO YourExistingTable (Columns)
select icn,
MAX(case when [sequence number] = 'a' then ISNULL([diagnosis code], '') end) as Admit
,MAX(case when [sequence number] = '01' then ISNULL([diagnosis code], '') end) as [Seq 1]
from dx1
group by icn
To answer your other question, it would create a table using the name you provided. If you specified the name as a temp table it would create a temp table. If you specified a permanent table...
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply