How to do UNPIVOT with dynamic column names within a FUNCTION

Question

Post reply

How to do UNPIVOT with dynamic column names within a FUNCTION

nemesis52

Valued Member

Points: 70
More actions
December 22, 2010 at 7:58 pm

#232281

Hi,
I have a problem and I hope you can help me. I need to use UNPIVOT inside a user defined function. The problem is, my column list should be dynamic. I tried to use EXEC (@SQL) to build my SELECT query and I found out it is impossible to use EXEC inside function so I don't know how I can make my query. I appreciate for you helps.
Thanks

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004642 More actions · Answer 1

nemesis52 (12/22/2010)
The problem is, my column list should be dynamic

I have to ask "Why"? Do you have so many tables to unpivot that you need dynamic SQL?

I think we might be able to help more if you told us more of the story and business reasons behind all of this.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

nemesis52 Valued Member Points: 70 More actions · Answer 2

Dear Jeff,

Thanks for your consideration. I try to give my story.

My project is to make a database for a microbiology lab. As one part of project I need to have a table for the result of antibiotic resistance test for each sample. So, I have table that the results of samples enter named "ANTI_RES". The structure of this table is dynamic which means for each antibiotic they test, the user should define it in another form and the specifications of each antibiotic save in a table named "ANTIBIOTICS" which has columns like "AB_NAME for antibiotic name, AB_CODE for acronym of each AB, etc.). When user define AB, a column is added dynamically to main table "ANTI_RES" with the name of acronym. This new column is an integer which accept three values based on the result of test which is ('1' for sensitive, '2' for intermediate and '3' for resistant). Finally, I will need to make a query with complex joins to see how many samples were resistant to each AB. So, I need to make function to have a table as output then I can use it like 'SELECT * FROM dbo.MyFunction()'. I can not make it in Stored Procedure and then insert it in a temp table at the time of use because the final usage in just one query inside an application so there is no way I can define and insert a temp table, everything should be just one 'SELECT' query.

Then, this is what I need to have inside the function :

DECLARE @FACTORS NVARCHAR (1000) /* This will contain list of columns in ANTI_RES table */

SELECT @FACTORS = substring((SELECT ( ', ' + AB_CODE ) FROM ANTIBIOTICS FOR XML PATH( '' )), 3, 1000 )

DECLARE @TestTable1 TABLE (ARCH_NO VARCHAR (12),

AB_NAME NVARCHAR (35),

RESULT [int])

INSERT INTO @TestTable1

SELECT x.ARCH_NO, a.AB_NAME, x.RESULT FROM

(SELECT ARCH_NO, FACTOR, RESULT

FROM (SELECT * FROM ANTI_RES) t

UNPIVOT (RESULT FOR FACTOR IN (@FACTORS)) AS u) AS x JOIN ANTIBIOTICS AS a ON

a.AB_CODE = x.FACTOR WHERE x.RESULT = '3'

SELECT * FROM @TestTable1

RETURN

END

The problem is ' UNPIVOT (RESULT FOR FACTOR IN (@FACTORS)) AS u) ' which is not acceptable.

I hope I don't confuse you. The last solution I have in mind is to use SELECT .... OPENQUERY() with linked servers but it has issues so I still prefer to find a possible way easier that that.

Thanks again

LutzM SSC Guru Points: 107049 More actions · Answer 3

Wouldn't it be more efficient to have one table ANTI_RES with a column AB_CODE, a column for the sensitivity result and one for the sample?

With such a structure you wouldn't have NULL values in columns if a specific test was not performed on a sample and you can easily design the pivoted structure you're using right now by applying the DynamicCrossTab method described in the related link in my signature.

Then you wouldn't have to worry about any new test the users come up with nad your code simply keeps running. Keep in mind: Lazyness (sometimes) will help to improve code 😀

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

nemesis52 Valued Member Points: 70 More actions · Answer 4

I solved my problem in another way. I put the function creation sentence inside a dynamic query and each time user define a new antibiotic, the function will be redefined with new list of columns.

Thanks for your tips.

Jeff Moden SSC Guru Points: 1004642 More actions · Answer 5

nemesis52 (12/23/2010)
I solved my problem in another way. I put the function creation sentence inside a dynamic query and each time user define a new antibiotic, the function will be redefined with new list of columns.
Thanks for your tips.

My question on that would be... what are you doing to keep the sproc from being used concurrently because you'll get some rather nasty returns when you do. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

nemesis52 Valued Member Points: 70 More actions · Answer 6

Hi Jeff,

Because there is just one admin who has permission and is responsible to enter basic definitions like antibiotics, I am not really worry about the issue you mentioned.

Thanks again for consideration

Jeff Moden SSC Guru Points: 1004642 More actions · Answer 7

nemesis52 (12/23/2010)
Hi Jeff,
Because there is just one admin who has permission and is responsible to enter basic definitions like antibiotics, I am not really worry about the issue you mentioned.
Thanks again for consideration

I appreciate the feedback. I wouldn't take the chance with the data, though. IF concurrent runs ever do happen (Murphy's law), you will have some seriously messed up data. Remember... humans are involved in this. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)