How to do UNPIVOT with dynamic column names within a FUNCTION

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.

Posted On: Tuesday 20th of November 2012 12:13:27 AM Total Views:  465
View Complete with Replies

Related Messages:

Issue with Exporting Data   (123 Views)
my SQL version is 5.1, I'm unable to export my query results to excel.....The Help Content instructs me to "right mouse" click on the "Query Results Grid" then select the Export Option, however, when I "right mouse" the mini menu does not appear..... Any assistance is greatly appreciated.
Help with UPDATE SQL   (122 Views)
Hi , In my Database (DB2), I have 3 tables. MDDT_BRANCHES MDDT_BRANCH_TIME_ON_HAND MDDT_CALCULATION_VARIABLES I need to update 1 field in MDDT_TABLES based on Values within all 3 tables. MDDT_BRANCHES.BRANCHID can be linked to MDDT_BRANCH_TIME_ON_HAND.ALLOCATEDTO MDDT_CALCULATION_VARIABLES is an Unrelated table with a single record. The end result I am looking for is to make the following: MDDT_BRANCHES.CURRENTWORKCAPACITY = (MDDT_BRANCHES.TIME * MDDT_CALCULATION_VARIABLES.DAYSTOKEEP) - MDDT_BRANCH_TIME_ON_HAND.SECONDSWOH I seem to be able to get Bits working, but cant get it all to work together
Need some help with a query asap please   (223 Views)
i have a temp table with data I want to insert records into the first table(tbl1) and use the ID field that is auto inc in tbl1 and insert some other data from tblTemp into tbl2 so i created a column to hold the identity from tbl1 in tblTemp named tbl1ID now I need to loop through the records inserting them and get the scope_identity of each record and insert that into the tblTemp.tbl1ID column help please how would I put this together Code: INSERT INTO tbl1 ( col1,col2 ) select col1,col2 FROM tblTemp and now i need to for each record INSERT INTO tblTemp SELECT @tbl1ID = SCOPE_IDENTITY() FROM tbl1
Query with temperorary tables?   (156 Views)
, ik have the following tables Order ----- Orderno customer note_id orderline ------------ ordern lineno note_id note_text chargeline ------------ orderno chargelineno note_id note -------- note_id note_text An order can have more than one orderline and more than one chargeorderline. You can store a text at orderlevel, orderlinelevel and at chargelinelevel (is not mandatory). I want to create a report which can show all these 3 text per order (and only shows an order when a text is available). I tried it with the following query which put the text for the orderlines and the text for the chargeorderlines in a temperorary table first. But this doesn't seem to work. Does anyone has an idea
Help with stored procedures in sql 2000   (211 Views)
HI , I was hoping someone would be able to help me with some thing. I am learning ASP.NEt v 2.0 and am using SQL server 2000 developerd edition to build an exommerce website using the Apress booking Beginning E-Commerce in C# 2005. I have been managing fine up to now but the book is building stored procedures for retrieving products lists from the database. The book is written for SQL 2005 express edition but says that with some extra woork there is no problems with getting the code to work in 2000. I have two procedures that implement paging which the book says doesn't work in sql 2000 and gives a very brief solution which doesn't make sense to me. Would anybody be able to help me convert these so i can use them in sql 2000 I get the rough idea but am having problems re-writing this and I am now holding my hands and admitting defeat. The procedures are as follows: Code: CREATE PROCEDURE GetProductsOnDepartmentPromotion (@DepartmentID INT, @DescriptionLength INT, @PageNumber INT, @ProductsPerPage INT, @HowManyProducts INT OUTPUT) AS -- declare a new TABLE variable DECLARE @Products TABLE (RowNumber INT, ProductID INT, Name VARCHAR(50), Description VARCHAR(5000), Price MONEY, Image1FileName VARCHAR(50), Image2FileName VARCHAR(50), OnDepartmentPromotion BIT, OnCatalogPromotion BIT) -- populate the table variable with the complete list of products INSERT INTO @Products SELECT ROW_NUMBER() OVER (ORDER BY ProductID) AS Row, ProductID, Name, SUBSTRING(Description, 1, @DescriptionLength) + '...' AS Description, Price, Image1FileName, Image2FileName, OnDepartmentPromotion, OnCatalogPromotion FROM (SELECT DISTINCT Product.ProductID, Product.Name, SUBSTRING(Product.Description, 1, @DescriptionLength) + '...' AS Description, Price, Image1FileName, Image2FileName, OnDepartmentPromotion, OnCatalogPromotion FROM Product INNER JOIN ProductCategory ON Product.ProductID = ProductCategory.ProductID INNER JOIN Category ON ProductCategory.CategoryID = Category.CategoryID WHERE Product.OnDepartmentPromotion = 1 AND Category.DepartmentID = @DepartmentID ) AS ProductOnDepPr -- return the total number of products using an OUTPUT variable SELECT @HowManyProducts = COUNT(ProductID) FROM @Products -- extract the requested page of products SELECT ProductID, Name, Description, Price, Image1FileName, Image2FileName, OnDepartmentPromotion, OnCatalogPromotion FROM @Products WHERE RowNumber > (@PageNumber - 1) * @ProductsPerPage AND RowNumber (@PageNumber - 1) * @ProductsPerPage AND RowNumber (@PageNumber - 1) * @ProductsPerPage AND RowNumber
Help with SQL statement!   (175 Views)
Hi im trying to write a SQL statement. I have attached a copy of the relationhips windows so you will understand. In the InterComp table I want to add up all InterComp_Quantity fields and also the InterComp_Cost. Which is easy. The problem is I want to group them by month i.e show the total Quantity and cost for each month and obvously I need to be able to seperate the years (In other words I dont want Jan 05 and Jan 06 added together I need Jan 05 & 06 totals shown seperately!). Is this possibly
hi with and some sql stuffs   (218 Views)
hi are there any smart willing to help a dumb girl out with a project in with and usage of sql. i have to make a small login page and some sort of sql login after that. but if u can help
I am a super noob to ASP and SQL but I am trying my best to learn. Please dont take this as a "please make a script for me!" post but rather someone asking for guidance: basically am i going the right way Is there an easier/better way of doing what I'm trying to achieve. Any links that you might have are also appreciated of course. now that i've gotten that out of the way... allow me to pose my situation: I am trying to create a form where users enters: nameemailGroup Name (just a variable text)upload a file (.doc)i also want the script to automatically enter in two more pieces of info into my table: timestamp for submission of docset status of .doc file to pendingand to provide the user with a unique ID for their submission so that they may enter it to update their .doc documents and upload them again. curretnly i have this as my code (i am using code from asp101 as a template) - please note that I am aware that I am not done, that is why I am here - asking for help. Code: ' primary code taken from 'Create an ADO connection to a database 'load the includes file for the conn's used
Help with setting up Many to Many relationship   (156 Views)
I am pretty new to SQL, but having fun learning at this point. One of the things I am trying to do is setup an asp page here at work that list some of the clients, and the contacts for them. Most of the sites have many contacts. A few of the contacts have many sites. How would I go about setting this up Currently, I have a table setup for sites and one for contacts. One the contacts table, I have a field for "site_id" and just relate it back for the site. This works fine to have multiple contacts associated to a site, but what do I do if the contact needs to be associated to more than one site
Recordset Problem with Absolutepage and adOpenStatic PLEASE HELP !!!!!!   (152 Views)
I have this script for navigating through a recordset, it all works fine, except when I click to move to the next page it displays all results again rather than the results only for that page. I've tried every combination of Record cursor types etc. and still no joy. Could someone please help me. Kind
need help with complicated select distinct query   (449 Views)
I have a table called gms_tests that keeps track of the results of tests run against servers. Columns are hostname, testname, status and time. The tests are run (about 5 against each server) every half hour, and the results are put in the table. I need to be able to search this table and obtain the most recent result for each test. For example, I need something similar to: select distinct hostname,testname from gms_tests order by time desc but I also need to be able to see the time and status columns. I've tried something like this: select hostname,testname,status,max(time) from gms_tests group by hostname,testname but the data returned in the status column is not correct - it doesn't match the status that corresponds with the latest test time. Any ideas what might work I'm using MySQL version 3.23.58, so I can't use subqueries.
Deleteing records with related tables   (135 Views)
once again. I seem to be getting this annoying error: Error Type: Microsoft OLE DB Provider for ODBC Drivers (0x80004005) [Microsoft][ODBC Microsoft Access Driver] The record cannot be deleted or changed because table 'tblMovieActor' includes related records. /deleteentry.asp, line 3 The record is related to a junction table ( tblMovieActor ). I have to delete the relation between the Movie and the Actor before I delete the movie, but I don't know how to querey the database and delete the relation. Can someone help out
Updating datetime value with NULL   (283 Views)
I'm trying to figure out how to insert a NULL value into a datetime column that already contains a date. The column contains NULL values by default before a date is inserted into it, but how can I insert NULL back into it
Need help with join   (248 Views)
I have 2 tables that I'd like to join based on a supplied project number. Table1: Columns = ProjectNum, Name example: 01011111, Test Project 01012222, Another Project Table2: Columns = ProjectNum, DescriptionCategory, Description example: 01011111, SD, The Short Description 01011111, LD, The Long Description 01012222, LD, Also a Long Description etc... I want to supply a project number and create a record for that project number from table1 that contains a Short Description column and a Long Description Column per record. ie. I supply 01011111 and get 01011111, Test Project, The Short Description, The Long Description. So far I am able to join the tables and get a record as long as records exist in Table2. If records don't exist in Table 2 then I get an empty result set. What I want is: I supply 01012222 and get: 01012222,Another Project,'',Also a Long Descrption
Problem with TIME field   (169 Views)
I am using SQL Server 2000. I made this following simple query: SELECT [TimeOperationBegan],[TimeOperationEnded], (DATEDIFF(minute,[TimeOperationBegan],[TimeOperationEnded])) AS Diftime FROM [ORRecord] The sample results are as follows: TimeOperationBegan |TimeOperationEnded |Diftime __________________________________________________ ________ 1899-12-30 10:40:00.000| 1899-12-30 11:22:00.000| 42 1899-12-30 11:41:00.000| 1899-12-30 11:52:00.000| 11 1899-12-30 16:03:00.000| 1899-12-30 16:32:00.000| 29 1899-12-30 23:05:00.000| 1899-12-30 00:32:00.000| -1353 How can i get rid of this negative value and make the query to calculate the difference between 23:05 and 24:32 for the fourth record
problems with LIKE query   (182 Views)
Hi I have an access database which has a table full of users and their scores and another table full of unacceptable user names. If a user tries to enter their name as "damn this game" i want the asp to spot that 'damn' is listed in my table (tblSwear) and replace the entire name with "anon". I read on 4 from rolla that when using a like statement if u place % around either side of the word u are comparing then it should allow for other characters appearing around the word. ie '%damn%' would find "blah-damn-blah" but for some reason my code below will only detect a user entering purely "damn" despite my use of the % symbol and it will not detect blahdamnblah" Code: dim thename thename=request("rude") sql = "SELECT * FROM tblSwear WHERE tblSwear.word LIKE '%" & thename & "%'" 'Select * from Tablename where (Firstname) LIKE '%" & FirstNameVar & "%'" sql,myconn,3,3 if rs.recordcount > 0 then response.Write("anon") else response.Write(thename) rs.close end if I keep staring at my code and i cant see what the mistake is, i have attached it and the database and files in case any1 has the time to give it a glance. I will be so grateful for any help.
trouble with query   (229 Views)
I have a database (Access 2000) that holds ship itinerary information and a list of businesses at ports that are visited I want to get a list businesses at the ports a ship visits over a period of time: Eg. Find all businesses in the ports that the Carnival Destiny visits over the next fortnight. --------------------------------------------------------------------------------- TABLES: ItMain: CID primary key Cruise ID Ship name of ship ItDates : CID Date dates an itinerary is repeated on e.g. 7-7-03, 7-14-03 etc ItPorts: CID Day cruise day (seven day cruise 0-1-2-3-4-5-6) Port port that ship visits BizMain: BID primary key - Business ID Port port that business is located in ----------------------------------------------------------------------------------- I thought this would be easy to solve, even with my poor knowledge of SQL. Alas, No! My subquery refused to cooperate! Can anyone post a solution for me, or even point me in the right direction (so that I can have the pleasure of solving it myself)
Deploy ASP application with MS SQL 7.0 data ?   (139 Views)
Originally posted by : MaliciousHUT (,I have developped a web site using MS SQL 7.0 as DBMS. Now I have to deploy but I dont know how to deploy all my data in database server. If I use SQL script , i can generate only database structure( table, trigger, stored procedure) not the data itself.Any ideas are appreciated
how can select distinct records within loop..??   (163 Views)
Originally posted by : dev ( wanna select distinct records within loop..i have got normalize database which carry one user table(which carryes unique records) and another user's skills(repeatetion of records) i made a search on user's skill table and stored in a record set(repeatation is there in the record set) query search result in record set called rs:userid = rs("userid")while not rs.eof userid = rs("userid")set rss = con.execute(select distinct(user) from user where userid = '"& userid &"')")rs.movenextwendwhen i execute this query it's does'nt give me distinct record i believe we cann't use distinct function in while loop bec'z every record is unique it self within loop. is there any way to select distinct records in loop
store carriage return with text in table   (151 Views)
Originally posted by : wk (,anybody knows the best menthod to store 2-3 paragraphs of text in SQL database How do i store the carriage return (enter) with the textthanx!