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:  401
View Complete with Replies

RELATED TOPICS OF MYSQL PROGRAMMING LANGUAGE




Issue with Exporting Data

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.
VIEWS ON THIS POST

104

Posted on:

Monday 19th November 2012
View Replies!

Help with UPDATE SQL

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
VIEWS ON THIS POST

104

Posted on:

Monday 19th November 2012
View Replies!

Need some help with a query asap please

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 \thelp 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
VIEWS ON THIS POST

191

Posted on:

Monday 19th November 2012
View Replies!

Query with temperorary tables?

, 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
VIEWS ON THIS POST

128

Posted on:

Monday 19th November 2012
View Replies!

Help with stored procedures in sql 2000

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
VIEWS ON THIS POST

172

Posted on:

Monday 19th November 2012
View Replies!

Help with SQL statement!

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
VIEWS ON THIS POST

149

Posted on:

Monday 19th November 2012
View Replies!

hi asp.net with vb.net and some sql stuffs

hi are there any smart willing to help a dumb girl out with a project in asp.net with vb.net 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
VIEWS ON THIS POST

191

Posted on:

Monday 19th November 2012
View Replies!


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 http://www.asp101.com 'Create an ADO connection to a database 'load the includes file for the conn's used
VIEWS ON THIS POST

216

Posted on:

Monday 19th November 2012
View Replies!

Help with setting up Many to Many relationship

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
VIEWS ON THIS POST

134

Posted on:

Monday 19th November 2012
View Replies!

Recordset Problem with Absolutepage and adOpenStatic PLEASE HELP !!!!!!

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
VIEWS ON THIS POST

130

Posted on:

Monday 19th November 2012
View Replies!

need help with complicated select distinct query

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.
VIEWS ON THIS POST

397

Posted on:

Monday 19th November 2012
View Replies!

Deleteing records with related tables

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
VIEWS ON THIS POST

115

Posted on:

Monday 19th November 2012
View Replies!

Updating datetime value with NULL

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
VIEWS ON THIS POST

257

Posted on:

Monday 19th November 2012
View Replies!

Need help with join

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
VIEWS ON THIS POST

219

Posted on:

Monday 19th November 2012
View Replies!

Problem with TIME field

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\t
VIEWS ON THIS POST

146

Posted on:

Tuesday 20th November 2012
View Replies!

problems with LIKE query

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 & "%'" rs.open 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.
VIEWS ON THIS POST

153

Posted on:

Tuesday 20th November 2012
View Replies!

trouble with query

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)
VIEWS ON THIS POST

197

Posted on:

Tuesday 20th November 2012
View Replies!

Deploy ASP application with MS SQL 7.0 data ?

Originally posted by : MaliciousHUT (MaliciousHUT@yahoo.co.uk),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
VIEWS ON THIS POST

120

Posted on:

Tuesday 20th November 2012
View Replies!

how can select distinct records within loop..??

Originally posted by : dev (dev0505@rediffmail.com)i 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 is1.got 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 \t\t\t
VIEWS ON THIS POST

142

Posted on:

Tuesday 20th November 2012
View Replies!

store carriage return with text in table

Originally posted by : wk (skrecycle@rediffmail.com),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!
VIEWS ON THIS POST

128

Posted on:

Tuesday 20th November 2012
View Replies!