How to assign variable to value

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

SQL Server 2008 Forums

Transact-SQL (2008)

How to assign variable to value

Author

Topic

scottstown1
Starting Member

15 Posts

Posted - 2012-03-22 : 11:47:56

Hello,

I have this SQL statement:

DECLARE
@ICDs VARCHAR(1000),
@n INT
SELECT @n = 1
SELECT @ICDs = COALESCE(@ICDs + ',', '')
+ case when isnumeric(diagnosis_code) = 0 then '''' else '' end
+ cast(diagnosis_code as varchar(10))
+ case when isnumeric(diagnosis_code) = 0 then '''' else '' end
+ 'code' +cast(@n as varchar(20)), @n = @n + 1
FROM diagnosis
WHERE (encounter_id = 34371)

Which works great, but I would like to now assign the column variables to value in the column beside that using a reference table. I would also like to use a plain select at the beginning which is not part of the loop at all.

An example might be:

fielda|fieldb|fieldc|code1|desc1|code2|desc2|code3|desc3... etc
asdfas|asdfas|asdfas|123.1|abcde|645.3|asdfe|98.23|aslkd... etc

Thank you very much for any help you can provide.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-03-22 : 12:40:39

Can you try explaining what you want to do again? You lost me at trying to assign "column variable" to a "value." Also I have no idea how a reference table or a loop fits into the scenario you are tryign to describe.

Is it possible you are talking about Aliasing a column name?

Here are some links that might help you present your queston in a way that we can help you better:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

scottstown1
Starting Member

15 Posts

Posted - 2012-03-22 : 14:27:50

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-03-22 : 14:42:19

There are probably some other ways like, pivoting or dynamic pivot or something that might be more extensable. But, here is one way:

SELECT 
    A.Code AS Code1, 
    A.Desc AS Desc1,
    B.Code AS Code2, 
    B.Desc AS Desc2,
    C.Code AS Code3, 
    C.Desc AS Desc3
    ...
FROM
    (
      <SQL statement currently outputs code1|code2|code3>
    ) AS Base
INNER JOIN
    ReferenceTable AS A
    ON Base.Code1 = A.Code        
INNER JOIN
    ReferenceTable AS B
    ON Base.Code2 = B.Code            
INNER JOIN
    ReferenceTable AS C
    ON Base.Code3 = C.Code

scottstown1
Starting Member

15 Posts

Posted - 2012-03-22 : 15:02:45

Thank you Lamprey for the reply.

I have tried to do what you suggested but it is giving an Incorrect syntax near '='. on the line SELECT @ICDs = COALESCE(@ICDs + ',', '')

Maybe I am not doing this right.

The reference table is called "icd9cm_ref" and the description column on that table is called "short_description"

DECLARE
@ICDs VARCHAR(1000),
@n INT
SELECT @n = 1

SELECT
A.Code AS Code1,
A.short_description AS Desc1,
B.Code AS Code2,
B.short_description AS Desc2,
C.Code AS Code3,
C.short_description AS Desc3
FROM
(

SELECT @ICDs = COALESCE(@ICDs + ',', '')
+ case when isnumeric(diagnosis_code) = 0 then '''' else '' end
+ cast(diagnosis_code as varchar(10))
+ case when isnumeric(diagnosis_code) = 0 then '''' else '' end
+ 'Code' +cast(@n as varchar(20)), @n = @n + 1
FROM diagnosis
WHERE (encounter_id = 34371)

)
AS Base
INNER JOIN
icd9cm_ref AS A
ON Base.Code1 = A.Code
INNER JOIN
icd9cm_ref AS B
ON Base.Code2 = B.Code
INNER JOIN
icd9cm_ref AS C
ON Base.Code3 = C.Code

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-03-22 : 15:10:06

If you read the links I posted before they talk about how to prepare sample data in a consumable format. If you want to provide sample data and expected output it'll make it a lot easier for us to help you. The query you've shown doesn't output three columns, so I'm rather confused.

scottstown1
Starting Member

15 Posts

Posted - 2012-03-22 : 15:59:17

---this is an example icd9cm_code reference table---
SELECT 123.45 as icd9cm_code, 'CHOLERA' as short_description
UNION
SELECT 156.45, 'CHOLERA VIBRIO CHOLERAE'
UNION
SELECT 255.4, 'TYPHOID PARATYPHOID FEVERS'

---this is an example of the main diagnosis table

SELECT 123.45 as diagnosis_code, '12345' as encounter_id
UNION
SELECT 156.45, '12345'
UNION
SELECT 255.4, '12345'

And the example output would be ( where encounter_id = 12345 )

code1|desc1|code2|desc2|code3|desc3
123.45|CHOLERA|156.45|CHOLERA VIBRIO CHOLERAE|255.4|TYPHOID PARATYPHOID FEVERS

Sorry and thank you for any help you can provide! :)

scottstown1
Starting Member

15 Posts

Posted - 2012-03-23 : 08:48:50

bump anyone?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources