TaskID PID task Milestone
83manasi task2
83manasi task3
83manasi task4
Above is the query of the result
I want to show this resultset horizontaly
for that I have to use the cursor such that
@M1=Where Milestone=1
@M2=Where Milestone=2
@M3=Where Milestone=3
and putting this into a temp table with its projectid and taskid.
Plz guide me that how to achiev this exactly
Swati
I have a query that calculates sales by sales person, but it displays horizontally across my query window. Is their a way in SQL Server to have the data display vertically down the window instead?
This is my current query
Code:
Select
count(case when salesman Like 'Geo%' then id else null end) As [George]
,count(case when salesman Like 'Li%' then id else null end) As [Lisa]
,count(case when salesman Like 'Jor%' then id else null end) As [Jorge]
,count(case when salesman Like 'Ri%' then id else null end) As [Richard]
,count(case when salesman Like 'Geo%' then id else null end)+count(case when salesman Like 'Li%' then id else null end) As [Team 1 Sales]
,count(case when salesman Like 'Jor%' then id else null end)+count(case when salesman Like 'Ri%' then id else null end) As [Team 2 Sales]
from sales.southeastregion
Which of course shows the results as such
George --- Lisa --- Jorge --- Richard --- Team 1 --- Team 2
100 50 10 90 150 100
And I want the data to be displayed like
George - 100
Lista - 50
Jorge - 10
Richard - 90
Team 1 - 150
Team 2 - 100
This is SQL Server 2008 if that matters.
I'm running SQL Server 2008 Standard.I need to create a query that has data from multiple columns (Columns 1-6 with coresponding Date started and Date Completed data) displayed vertically, but also has the column name the preeceeding column to identify it...along with other data (Record number, status).
Record Number, Status, Column Name, Date Started, DateCompleted
1, Open, Column 1, 1/1/2012, 2/1/2012,
2, Hold, Column 2, 1/3/2012, 3/1/2012,
1, Open, Column 3, 2/5/2012, 4/6/2012,
3, Closed, Column 4, 5/10/2012, 7/25/2012,
2, Hold, Column 5, 3/9/2012, 4/1/2012,
1, open, Column 6, 10/10/2012, 12/12/2012,
id name
--- -----------------
236 SERVICE REQUEST
236 HARDWARE
236 Desktop
336 Loan
id name
-- ----------------------------
236 SERVICE REQUEST/ HARDWARE/ Desktop/Laptop/ Loan
I have a table like this:
weight type factory1 factory2 factory3 factory4.... to factory 150
1kg goods 5.00 5.50 5.20 5.00...
2kg goods 6.00 6.20 6.15 6.30...
3kg goods 4.00 4.50 5.00 4.30...
...
and would like to extract data this way:
producer type weight price
factory1 goods 1kg 5.00
factory1 goods 2kg 6.00
factory1 goods 3kg 4.00
factory1.....
then
factory2 goods 1kg 5.50
factory2 goods 2kg 6.20
and so on for all factories.
I tried with UNPIVOT but it does not allow it (I'm using Navicat 8), saying "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near UNPIVOT...".
Hi all
I dont want to use functions or proceedures but with select itself I want to get the above mentioned result. Can somebody help
SURESH IYER
I have a table as follows
opendate (datetime) callnumber (int) closed (bit)
I want to find how many calls were opened today and of those how many are closed
I have come up with the code below but again am looking for
1. a more elegant solution
2. a way to generalise this to show the same information for x number of days
create table #holdit1
(opencount int)
create table #holdit2
(closedcount int)
insert into #holdit1
SELECT count(*) as opencount
FROM [dbo].[problog]
WHERE datediff(dd, opendate, getdate()) = 0
AND closed = 0
group by closed
insert into #holdit2
SELECT count(*) as closedcount
FROM [dbo].[problog]
WHERE datediff(dd, opendate, getdate()) = 0
AND closed = 1
group by closed
select #holdit1.opencount AS CallsOpen, #holdit2.closedcount AS CallsClosed, #holdit1.opencount + #holdit2.closedcount AS AllCalls
from #holdit1 cross join #holdit2 #holdit2
DROP TABLE #holdit1
DROP TABLE #holdit2
this gives me
CallsOpen CallsClosed AllCalls
----------- ----------- -----------
1 3 4
Hi,
What I need is the following, a StoredProc that make a Vertical (normal table) into a Horizontal Table :
So From:
CustNo__Item
__1______1
__1______3
__1______4
__2______2
__2______3
__2______7
To:
CustNo___1__2___3___4___7
_1_______X______X___X
_2__________X___X_______X
Anny help would be verry appriciated
I want to append vertical row result into horizontal
CREATE TABLE #mable(mid INT, token nvarchar(16))
INSERT INTO #mable VALUES (0, 'foo')
INSERT INTO #mable VALUES(0, 'goo')
INSERT INTO #mable VALUES(1, 'hoo')
INSERT INTO #mable VALUES(1, 'moo')
Actual output
[code]....
From:
Cust | Qstion | Answer
_________________________________
1,1,Bike
1,4,Blue
1,6,No ensurance
2,1,Car
2,3,silver
2,9,ensurance
2,11,Yes
To the following
Cust______1______3______4______6______9______11
__________________________________________________ __________
1_______Bike___________Blue__No ENsur_________
2_______car____silver_________________Ens____Yes"
create table #temp1
(
col1 float,
col2 float,
col3 float,
clo4 float)
insert into #temp1 values (1.5, 1.6,1.7,1.8)
insert into #temp1 values (1.9, 1.0,1.2,1.8)
o/p should display as below is there a way we can do this with cte or some other option
col1 1.5 1.9
col2 1.6 1.0
col3 1.7 1.2
col4 1.8 1.8
Month Discount
Expenses GST
<gs class="GINGER_SOFTWARE_mark" ginger_software_uiphraseguid="d14582be-eecd-4def-be7e-0cfe00b13c80" id="4279c12f-e03f-4b38-9fc8-eb1a991c25ac"><gs class="GINGER_SOFTWARE_mark" ginger_software_uiphraseguid="7f0da910-3775-4ee4-821e-1a0e7ee2ce0b"
id="92ce074f-ee0b-4794-839b-ee50c88d380c">ServiceCharge</gs></gs>
[Code] ....
Is it possible to align a text box to display text with rotation? For example can I display it rotated 90 degrees left so that it reads from the bottom towards the top of the page? All I can find is left/right alignment and top/middle/bottom alignment. I want to rotate the text.
Thanks.
Hello I was very happy to have found the thread http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=491642&SiteID=1 that explained how to get the text to display Bottom-to-Top/Left-to-Right.
The solution was to setup a function that creates a bitmap the text to be displayed.
This works well and correctly displays the text image in HTML and PDF. (Excel, XML and CVS won't export backgroud images).
To extend the solution to wrap text it requires a few additional lines...
Code Snippet
Function LoadImage3(ByVal stText As String)
stText = stText.PadRight(10)
stText = stText.PadLeft(10)
Dim iMaxLength as Integer = 180
Dim iMaxWidth as Integer = 180
Dim f As Drawing.Font = New Drawing.Font("Arial",7, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point)
Dim bmpImage As New Drawing.Bitmap(1, 1)
Dim MyGraphics As Drawing.Graphics = Drawing.Graphics.FromImage(bmpImage)
Dim imageSize As Drawing.SizeF = MyGraphics.MeasureString(stText, f)
Dim i As New System.Drawing.Bitmap(iMaxWidth, iMaxLength)
Dim g As Drawing.Graphics = Drawing.Graphics.FromImage(i)
Dim rectF1 As New Drawing.Rectangle(-(iMaxWidth/2),-(iMaxLength/2),iMaxWidth,iMaxLength )
g.FillRectangle(Drawing.Brushes.White, 0, 0, i.Width, i.Height)
g.TranslateTransform((iMaxWidth/2), (iMaxLength/2) )
g.RotateTransform(270.0F) 'flip the image 270 degrees
g.DrawString(stText, f,Drawing.Brushes.Black, rectF1)
g.DrawRectangle(New Drawing.Pen(Drawing.Color.White, 1), rectF1)
g.Flush()
Dim stream As IO.MemoryStream = New IO.MemoryStream
Dim bitmapBytes As Byte()
i.Save(stream, System.Drawing.Imaging.ImageFormat.Jpeg) 'Create bitmap
bitmapBytes = stream.ToArray
stream.Close()
i.Dispose()
Return bitmapBytes
End Function
Items highlighted in yellow reflect changes made to orignal solution.
Hope this helps!!
Could anybody tell me how to convert vertical data into horizontal data?I have a one-to-many relationship in sql server 2KProduct, ProductAccessory, one Product has many ProductAccessories.My Table design is like this:Table Product{ ProdId int, ProdNameId int, ....}Table ProductAccessory{ ProdId int, AccNameId int, AccUnitId int, ....}Because one Production has at most 4 ProductAccessoryI want to use a SELECT statement OR function to return ProdId, ProdNameId, AccNameId1, AccUnitId1, AccNameId2, AccUnitId2, AccNameId3, AccUnitId3, ....Any help will be appreciated! Thanks a lotJoseph
View 3 Replies View RelatedHi All,
Any assistance would be greatly appreciated.
I have a current table which I create on a regular basis from a text file with a layout similar to this:
TypePolicy #AmountRider 1 AmtRider 2 Amt
B1112H24.341212.34
This text file is brought into a staging table with each field (even the amount field) as a varchar (12). I then assign types in a later step in my DTS package.
What I need to do is stack the riders under each policy so for each policy where there is a rider, there is a new row for every rider.
So in the example I've given, there would be 2 additional rows for the original first row since there are two riders.
TypePolicy #Amount
B1112H24.34
R11112H12
R21112H12.34
I plan on doing this by first creating a table with just the Type, Policy #, and Amt fields, and then using a series of insert queries where I take the rider (if there is one) and append it onto the table.
However, I'm getting the following error message when I try:
Server: Msg 213, Level 16, State 4, Line 1
Insert Error: Column name or number of supplied values does not match table definition.
Basically, it wouldn't let me put an 'R1' in the Type column.
How can I get this to work!?!?
Thanks in advance for your help
Whenever I open a new query I see 3 vertical lines on the screen, from top to button. Did a full reinstall, and they are still there. How to get rid of them?
View 7 Replies View RelatedI have the following SQL query that I like to view the out put in horizontal format:
Select ID, First_name, Last_name from ABC
Instead of getting out-put like
Id First_name Last_name
1Jim Smith
2Tom Jones
I like to see the out-put like:
Id1 2
First_nameJimSmith
Last_nameTomJones
Please advice.
Thanks in advance,
Abrahim
I have some data which is vertical...I want to create a pivot query in SQL that will give me a result that is horizontal like this. I cannot find a way of doing it without lots of IF or CASE statements?
View 10 Replies View RelatedWhile using Aggregate Transformation to group one column,the rows of output sometimes larger than the rows returned by a T-SQL statement via SSMS.
For example,the output of the Aggregate Transformation may be 960216 ,but the
'Select Count(Orderid) From ... Group By ***' T-SQL Statement returns 96018*.
I'm sure the Group By of the Aggregate Transformation is right!
But ,when I set the "keyscale" property of the transformation,the results match!
In my opinion,the "keyscale" property will jsut affects the performance of the transformaiton,but not the result of the transformation.
Thanks for your advice.
Hi,
I have a customer who is using vba to pull a result set from an sql server stored procedure into excel. She wants a calculated column added to the result set that gives:
The number days (datediff) between the end date (autend_dte) on one row and the begin date (autbeg_dte) on the next row for each client (clt_num). The rows are to be ordered by client and begin date. The number should be associated with the second row used to calculate the date diff. The first row for each client will have a date diff of 0.
I could do this using a cursor in the stored procedure or a loop in the vba, but I would prefer to do it with the select, but I don't even know where to start.
See expected results below.
CREATE TABLE #testit (
clt_num int NOT NULL ,
autbeg_dte datetime NULL ,
autend_dte datetime NULL)
INSERT INTO #testit (clt_num, autbeg_dte, autend_dte)
SELECT 510, '2004-09-01 00:00:00.000', '2005-09-30 23:59:00.000' UNION ALL
SELECT 510, '2005-10-01 00:00:00.000', '2006-04-06 23:59:00.000' UNION ALL
SELECT 600, '2006-08-01 00:00:00.000', '2006-11-06 23:59:00.000' UNION ALL
SELECT 2529, '2006-01-13 00:00:00.000', '2006-04-11 23:59:00.000' UNION ALL
SELECT 2529, '2005-11-30 00:00:00.000', '2005-12-12 23:59:00.000' UNION ALL
SELECT 2602, '2006-03-29 00:00:00.000', '2006-05-02 23:59:00.000' UNION ALL
SELECT 2602, '2005-11-12 00:00:00.000', '2006-02-27 23:59:00.000' UNION ALL
SELECT 2602, '2006-05-26 00:00:00.000', '2006-06-12 23:59:00.000' UNION ALL
SELECT 2602, '2006-06-18 00:00:00.000', '2006-06-28 23:59:00.000'
SELECT * FROM #testit
order by clt_num,autbeg_dte
Expected result:
clt_numautbeg_dte autend_dte Days Diff
5102004-09-01 00:00:00.0002005-09-30 23:59:00.0000
5102005-10-01 00:00:00.0002006-04-06 23:59:00.0001
6002006-08-01 00:00:00.000 2006-11-06 23:59:00.000 0
25292005-11-30 00:00:00.0002005-12-12 23:59:00.0000
25292006-01-13 00:00:00.0002006-04-11 23:59:00.00044
26022005-11-12 00:00:00.0002006-02-27 23:59:00.0000
26022006-03-29 00:00:00.0002006-05-02 23:59:00.00030
26022006-05-26 00:00:00.0002006-06-12 23:59:00.00024
26022006-06-18 00:00:00.0002006-06-28 23:59:00.0006
Thanks,
Laurie
hi...
In DSV, i am creating a "named Query", can we use 'cursors' while creating a named query ???
Thanks in advance ..
Hi!Server info -Win2K3 Server +SP1 with 1 GB Memory and 1.5 GB Virtual MemorySQL Server 2000 Enterprise Edition + SP3 running on this.Required result -Create a SQL Script that will contain a set of create, update, insert& delete statements (about 17500 lines including blank lines) thatcan be run on different databases seperatelyHow we do this -We have a SP - that creates a temporary table and then calls anotherSP that actually populates the temporary table created by the first SP*Samples of both SPs are below -PROBLEMThe result is directed to a file -However when the query is run it runs through the entire script but'Jumbles' the outputRunning the same scripts on a copy of the database on other machineswork fine and the size of the outfiles is exactly the sameI have increased the page size to 2.5 GB and restarted the server.Running the sp now generated the correct output a few times but gotjumbled as before after a few more users logged in and activity on theserver increased.Another interesting point is that the output is jumbled exactly thesame way each time. It seems the sql executes correctly and writesthe output in chunks only writting the chunks out of sequence - butin the same sequence each time.e.g. of expected resultInsert into Table1Values x, y, z, 1, 2Insert into Table1Values q, s, g, 3, 4Insert into Table1Values c, d, e, 21, 12....Insert into Table2Values ...Insert into Table3Values ................Update RefGenSet Last = 1234Where RefGenRef = 1JUMBLED OUTPUTInsert into Table1Values x, y, z, 1, 2Insert into Table1Values q, s, g, 3, 4Insert into Table1Values c, d, e, 21, 12....Insert into Table2Values ...Insert into Table2Values ...Values ...Update RefGenSet Last = 1234Where RefGenRef = 1Insert into Table3Values ................Insert into Table1Values c, d, e, 21, 12....Insert into Table2----------------------------------------Sample of First Script - STATDATA_RSLT**************************************SET QUOTED_IDENTIFIER ONGOSET ANSI_NULLS ONGOSET NOCOUNT ONGOCREATE PROCEDURE StatData_rsltASCREATE TABLE #tbl_Script(ScriptText varchar(4000))EXEC TestStatData_intSELECT t.ScriptTextFROM #tbl_Script tGOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ONGO*******************************************Sample of CALLED SP - TestStatData_int*******************************************SET QUOTED_IDENTIFIER ONGOSET ANSI_NULLS ONGOCREATE PROCEDURE TestStatData_intASDECLARE @AttrRef int,@TestID int,@PrtTestRef int,@AttrType tinyint,@EdtblSw tinyint,@NmValRef int,@SrtTypeRef int,@AttrStr varchar(20),@TestStr varchar(20),@PrtTestStr varchar(20),@AttrTypeStr varchar(20),@EdtblStr varchar(20),@NmValStr varchar(20),@SrtTypeStr varchar(20),@TestRef int,@Seq int,@PrtRef int,@Value varchar(255),@TermDate datetime,@AttrID int,@DefSw tinyint,@WantSw tinyint,@TestRefStr varchar(20),@SeqStr varchar(20),@PrtStr varchar(20),@TermDateStr varchar(255),@AttrIDStr varchar(20),@DefStr varchar(20),@WantStr varchar(20),@LanRef int,@LanStr varchar(20),@Code varchar(20),@Desc varchar(255),@MultiCode varchar(20),@MultiDesc varchar(255),@InhSw tinyint,@InhStr varchar(20),@InhFrom int,@InhFromStr varchar(20),@Lan_TestRef int,@ActSw tinyint,@ActSwStr varchar(20)SELECT @Lan_TestRef = dbo.fn_GetTestRef('Lan')INSERT INTO #tbl_ScriptVALUES('')-- Create tablesINSERT INTO #tbl_ScriptVALUES ('CREATE TABLE #tbl_Test (AttrRef int, TestID int , PrtTestRefint, AttrType tinyint, EdtblSw tinyint, NmValRef int, SrtTypeRefint)')INSERT INTO #tbl_ScriptVALUES ('')INSERT INTO #tbl_ScriptVALUES('CREATE TABLE #tbl_TestAttr(AttrRef int, TestRef int, Seq int,PrtRef int, AttrType tinyint, Value varchar(255), TermDate datetime,AttrID int, DefSw tinyint, WantSw tinyint, ActSw tinyint)')INSERT INTO #tbl_ScriptVALUES ('')INSERT INTO #tbl_ScriptVALUES ('CREATE TABLE #tbl_AttrName(AttrRef int, LanRef int, Codevarchar(20), [Desc] varchar(255), MultiCode varchar(20), MultiDescvarchar(255), InhSw tinyint, InhFrom int)')INSERT INTO #tbl_ScriptVALUES ('')-- insert Test valuesDECLARE Test_cursor CURSOR FORSELECT l.AttrRef, l.TestID, l.PrtTestRef, l.AttrType, l.EdtblSw,l.NmValRef, l.SrtTypeRefFROM Test lOPEN Test_cursorFETCH NEXT FROM Test_cursorINTO @AttrRef, @TestID, @PrtTestRef, @AttrType, @EdtblSw, @NmValRef,@SrtTypeRefWHILE @@FETCH_STATUS = 0BEGINSELECT @AttrStr = ISNULL(CAST(@AttrRef as varchar), 'NULL'),@TestStr = ISNULL(CAST(@TestID as varchar), 'NULL'),@PrtTestStr = ISNULL(CAST(@PrtTestRef as varchar), 'NULL'),@AttrTypeStr = ISNULL(CAST(@AttrType as varchar), 'NULL'),@EdtblStr = ISNULL(CAST(@EdtblSw as varchar), 'NULL'),@NmValStr = ISNULL(CAST(@NmValRef as varchar), 'NULL'),@SrtTypeStr = ISNULL(CAST(@SrtTypeRef as varchar), 'NULL')INSERT INTO #tbl_ScriptVALUES ('INSERT INTO #tbl_Test(AttrRef, TestID, PrtTestRef,AttrType,EdtblSw, NmValRef, SrtTypeRef)')INSERT INTO #tbl_ScriptVALUES ('VALUES ( ' + @AttrStr + ', ' + @TestStr + ', ' +@PrtTestStr+ ', ' + @AttrTypeStr + ', ' + @EdtblStr + ', ' + @NmValStr + ', ' +@SrtTypeStr + ')')INSERT INTO #tbl_ScriptVALUES ('')FETCH NEXT FROM Test_cursorINTO @AttrRef, @TestID, @PrtTestRef, @AttrType, @EdtblSw, @NmValRef,@SrtTypeRefENDCLOSE Test_cursorDEALLOCATE Test_cursorDECLARE TestAttr_cursor CURSOR FORSELECT le.AttrRef, le.TestRef, le.Seq, le.PrtRef, le.AttrType,le.Value,le.TermDate, le.AttrID, le.DefSw, le.WantSw, le.ActSwFROM TestAttr leWHERE le.WantSw = 1AND le.ActSw = 1OPEN TestAttr_cursorFETCH NEXT FROM TestAttr_cursorINTO @AttrRef, @TestRef, @Seq, @PrtRef, @AttrType, @Value,@TermDate, @AttrID, @DefSw, @WantSw, @ActSwWHILE @@FETCH_STATUS = 0BEGINSELECT @AttrStr = ISNULL(CAST(@AttrRef as varchar), 'NULL'),@TestRefStr = ISNULL(CAST(@TestRef as varchar), 'NULL'),@SeqStr = ISNULL(CAST(@Seq as varchar), 'NULL'),@PrtStr = ISNULL(CAST(@PrtRef as varchar), 'NULL'),@AttrTypeStr = ISNULL(CAST(@AttrType as varchar), 'NULL'),@Value = ISNULL(@Value, 'NULL'),@TermDateStr = ISNULL(CAST(@TermDate as varchar), 'NULL'),@AttrIDStr = ISNULL(CAST(@AttrID as varchar), 'NULL'),@DefStr = ISNULL(CAST(@DefSw as varchar), 'NULL'),@WantStr = ISNULL(CAST(@WantSw as varchar), 'NULL'),@ActSwStr = ISNULL(CAST(@ActSw as varchar), '1')SELECT @Value = '''' + @Value + ''''WHERE @Value <> 'NULL'INSERT INTO #tbl_ScriptVALUES ('INSERT INTO #tbl_TestAttr(AttrRef, TestRef, Seq, PrtRef,AttrType, Value, TermDate, AttrID, DefSw, WantSw, ActSw)')INSERT INTO #tbl_ScriptVALUES ('VALUES (' + @AttrStr + ', ' + @TestRefStr + ', ' +@SeqStr+ ', ' + @PrtStr + ', ' + @AttrTypeStr + ', ' + @Value + ', ' +@TermDateStr + ', ' + @AttrIDStr + ', ' + @DefStr + ', ' + @WantStr+', '+ @ActSwStr + ')')INSERT INTO #tbl_ScriptVALUES ('')FETCH NEXT FROM TestAttr_cursorINTO @AttrRef, @TestRef, @Seq, @PrtRef, @AttrType, @Value,@TermDate, @AttrID, @DefSw, @WantSw, @ActSwENDCLOSE TestAttr_cursorDEALLOCATE TestAttr_cursorDECLARE AttrName_cursor CURSOR FORSELECT e.AttrRef, e.LanRef, e.Code, e.[Desc], e.MultiCode,e.MultiDesc, e.InhSw, e.InhFromFROM AttrName e, TestAttr leWHERE e.LanRef = 0AND e.AttrRef = le.AttrRefAND le.WantSw = 1AND le.ActSw = 1OPEN AttrName_cursorFETCH NEXT FROM AttrName_cursorINTO @AttrRef, @LanRef, @Code, @Desc, @MultiCode,@MultiDesc, @InhSw, @InhFromWHILE @@FETCH_STATUS = 0BEGINSELECT @AttrStr = ISNULL(CAST(@AttrRef as varchar), 'NULL'),@LanStr = ISNULL(CAST(@LanRef as varchar), 'NULL'),@Code = ISNULL(@Code, 'NULL'),@Desc = ISNULL(@Desc, 'NULL'),@MultiCode = ISNULL(@MultiCode, 'NULL'),@MultiDesc = ISNULL(@MultiDesc, 'NULL'),@InhStr = ISNULL(CAST(@InhSw as varchar), 'NULL'),@InhFromStr = ISNULL(CAST(@InhFrom as varchar), 'NULL')SELECT @Code = REPLACE(@Code, '''',''''''),@Desc = REPLACE(@Desc, '''','''''') ,@MultiCode = REPLACE(@MultiCode, '''','''''') ,@MultiDesc = REPLACE(@MultiDesc, '''','''''')INSERT INTO #tbl_ScriptVALUES ('INSERT INTO #tbl_AttrName(AttrRef, LanRef, Code, [Desc],MultiCode, MultiDesc, InhSw, InhFrom)')INSERT INTO #tbl_ScriptVALUES ('VALUES (' + @AttrStr + ', ' + @LanStr + ', ''' + @Code +''', ''' + @Desc + ''', ''' + @MultiCode + ''', ''' + @MultiDesc +''',' + @InhStr + ', ' + @InhFromStr + ')')INSERT INTO #tbl_ScriptVALUES ('')FETCH NEXT FROM AttrName_cursorINTO @AttrRef, @LanRef, @Code, @Desc, @MultiCode,@MultiDesc, @InhSw, @InhFromENDCLOSE AttrName_cursorDEALLOCATE AttrName_cursor-- Do update TestAttr dataINSERT INTO #tbl_ScriptVALUES ('UPDATE le')INSERT INTO #tbl_ScriptVALUES ('SET')INSERT INTO #tbl_ScriptVALUES (' le.TestRef = t.TestRef,')INSERT INTO #tbl_ScriptVALUES (' le.PrtRef = t.PrtRef,')INSERT INTO #tbl_ScriptVALUES (' le.AttrType = t.AttrType,')INSERT INTO #tbl_ScriptVALUES (' le.Value = t.Value,')INSERT INTO #tbl_ScriptVALUES (' le.TermDate = t.TermDate,')INSERT INTO #tbl_ScriptVALUES (' le.AttrID = t.AttrID,')INSERT INTO #tbl_ScriptVALUES (' le.DefSw = t.DefSw,')INSERT INTO #tbl_ScriptVALUES (' le.WantSw = t.WantSw,')INSERT INTO #tbl_ScriptVALUES (' le.ActSw = t.ActSw')INSERT INTO #tbl_ScriptVALUES ('FROM TestAttr le, #tbl_TestAttr t')INSERT INTO #tbl_ScriptVALUES ('WHERE le.AttrRef = t.AttrRef')INSERT INTO #tbl_ScriptVALUES ('')-- Update AttrNameINSERT INTO #tbl_ScriptVALUES ('UPDATE en')INSERT INTO #tbl_ScriptVALUES ('SET')INSERT INTO #tbl_ScriptVALUES (' en.Code = te.Code,')INSERT INTO #tbl_ScriptVALUES (' en.[Desc] = te.[Desc],')INSERT INTO #tbl_ScriptVALUES (' en.MultiCode = te.MultiCode,')INSERT INTO #tbl_ScriptVALUES (' en.MultiDesc = te.MultiDesc,')INSERT INTO #tbl_ScriptVALUES (' en.InhSw = te.InhSw,')INSERT INTO #tbl_ScriptVALUES (' en.InhFrom = te.InhFrom')INSERT INTO #tbl_ScriptVALUES ('FROM AttrName en, #tbl_AttrName te')INSERT INTO #tbl_ScriptVALUES ('WHERE en.AttrRef = te.AttrRef')INSERT INTO #tbl_ScriptVALUES (' AND en.LanRef = te.LanRef')INSERT INTO #tbl_ScriptVALUES (' AND te.LanRef = 0')-- Do update Test the dataINSERT INTO #tbl_ScriptVALUES ('UPDATE l')INSERT INTO #tbl_ScriptVALUES ('SET')INSERT INTO #tbl_ScriptVALUES (' l.TestID = t.TestID,')INSERT INTO #tbl_ScriptVALUES (' l.PrtTestRef = t.PrtTestRef,')INSERT INTO #tbl_ScriptVALUES (' l.AttrType = t.AttrType,')INSERT INTO #tbl_ScriptVALUES (' l.EdtblSw = t.EdtblSw,')INSERT INTO #tbl_ScriptVALUES (' l.NmValRef = t.NmValRef')INSERT INTO #tbl_ScriptVALUES ('FROM Test l, #tbl_Test t')INSERT INTO #tbl_ScriptVALUES ('WHERE l.AttrRef = t.AttrRef')INSERT INTO #tbl_ScriptVALUES ('')--DELETE where just updatedINSERT INTO #tbl_ScriptVALUES ('DELETE FROM t')INSERT INTO #tbl_ScriptVALUES ('FROM #tbl_Test t, Test l')INSERT INTO #tbl_ScriptVALUES ('WHERE t.AttrRef = l.AttrRef')INSERT INTO #tbl_ScriptVALUES ('')INSERT INTO #tbl_ScriptVALUES ('DELETE FROM t')INSERT INTO #tbl_ScriptVALUES ('FROM #tbl_TestAttr t, TestAttr le')INSERT INTO #tbl_ScriptVALUES ('WHERE t.AttrRef = le.AttrRef')INSERT INTO #tbl_ScriptVALUES ('')INSERT INTO #tbl_ScriptVALUES ('DELETE FROM te')INSERT INTO #tbl_ScriptVALUES ('FROM #tbl_AttrName te, TestAttr le')INSERT INTO #tbl_ScriptVALUES ('WHERE te.AttrRef = le.AttrRef')INSERT INTO #tbl_ScriptVALUES ('')-- Insert TestAttrINSERT INTO #tbl_ScriptVALUES ('INSERT INTO TestAttr (AttrRef, TestRef, Seq, PrtRef,AttrType,Value, TermDate, AttrID, DefSw, WantSw, ActSw)')INSERT INTO #tbl_ScriptVALUES ('SELECT t.AttrRef, t.TestRef, t.Seq, t.PrtRef, t.AttrType,t.Value, t.TermDate, t.AttrID, t.DefSw, t.WantSw, t.ActSw')INSERT INTO #tbl_ScriptVALUES ('FROM #tbl_TestAttr t')INSERT INTO #tbl_ScriptVALUES ('')-- AttrNameINSERT INTO #tbl_ScriptVALUES ('INSERT INTO AttrName(AttrRef, LanRef, Code, [Desc],MultiCode,MultiDesc, InhSw, InhFrom)')INSERT INTO #tbl_ScriptVALUES ('SELECT te.AttrRef, le.AttrRef, te.Code, te.[Desc],te.MultiCode, te.MultiDesc, ')INSERT INTO #tbl_ScriptVALUES (' CASE le.AttrRef ')INSERT INTO #tbl_ScriptVALUES (' WHEN 0 THEN 0')INSERT INTO #tbl_ScriptVALUES (' ELSE 1 END,')INSERT INTO #tbl_ScriptVALUES (' CASE le.AttrRef ')INSERT INTO #tbl_ScriptVALUES (' WHEN 0 THEN NULL')INSERT INTO #tbl_ScriptVALUES (' ELSE 0 END')INSERT INTO #tbl_ScriptVALUES ('FROM #tbl_AttrName te, TestAttr le')INSERT INTO #tbl_ScriptVALUES ('WHERE le.TestRef = ' + CAST(@Lan_TestRef as varchar))INSERT INTO #tbl_ScriptVALUES ('')-- Insert new rowsINSERT INTO #tbl_ScriptVALUES ('INSERT INTO Test(AttrRef, TestID, PrtTestRef, AttrType,EdtblSw, NmValRef, SrtTypeRef)')INSERT INTO #tbl_ScriptVALUES ('SELECT t.AttrRef, t.TestID, t.PrtTestRef, t.AttrType,t.EdtblSw, t.NmValRef, t.SrtTypeRef')INSERT INTO #tbl_ScriptVALUES ('FROM #tbl_Test t')INSERT INTO #tbl_ScriptVALUES ('')INSERT INTO #tbl_ScriptVALUES ('DROP TABLE #tbl_Test')INSERT INTO #tbl_ScriptVALUES ('DROP TABLE #tbl_TestAttr')INSERT INTO #tbl_ScriptVALUES ('DROP TABLE #tbl_AttrName')-- Update RefGenDECLARE @RefGenReflast int,@RefGenRefStr varchar(10)SELECT @RefGenReflast = lastFROM RefGenWHERE RefGenRef = 1SELECT @RefGenRefStr = ISNULL(CAST(@RefGenReflast as varchar), 'NULL')INSERT INTO #tbl_ScriptVALUES('')INSERT INTO #tbl_ScriptVALUES('UPDATE RefGen')INSERT INTO #tbl_ScriptVALUES ('SET Last = ' + @RefGenRefStr)INSERT INTO #tbl_ScriptVALUES ('WHERE RefGenRef = 1')INSERT INTO #tbl_ScriptVALUES ('')GOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ONGO*******************************RegardsGlenn
View 5 Replies View RelatedHi all.
I am stuck in a bit of a conundrum for quite a while now, and I hope someone here will help me figure this one out.
So, first things first: let me explain what I need to do. I am
designing a web application that will allow users to consult info
available in a SQL2000 database. The user will enter the search
criterea, and hopefully the web page will show matching results.
The problem is the results shown aren't available per se in the DB, I
need to process the data a bit. I decided to do so on the SQL Server
side, though the use of cursors. So, when a user defines his search
criteria, I run a stored procedure that begins by building a dynamic
sql query and creating a cursor for it. I used a global cursor in order
to do so. It looked something like this:
SET @sqlQuery = ... (build the dinamic sql query)
SET @cursorQuery = 'DECLARE myCursor CURSOR GLOBAL FAST_FORWARD FOR ' + @sqlQuery
EXEC @cursorQuery
OPEN myCursor
FETCH NEXT FROM myCursor INTO ...
CLOSE myCursor
DEALLOCATE myCursor
This works fine, if there's only one instance of the
stored procedure running at a time. Should another user connect to the
site and run a search while someone's at it, it'll fail due to the
atempt to create a cursor with the same name.
My first thought was to make the cursor name unique, which led me to:
...
SET @cursorName = 'myCursor' + @uniqueUserID
SET @cursorQuery = 'DECLARE '+ @cursorName + 'CURSOR FAST_FORWARD FOR ' + @sqlQuery
EXEC @cursorQuery
...
The problem with this is that I can't do a FETCH NEXT FROM @cursorName since
@cursorName is a char variable holding the cursor name, and not a
cursor variable. So to enforce this unique name method the only option
I have is to keep creating dynamic sql queries and exucting them. And
this makes the sp a bitch to develop and maintain, and I'm guessing it
doesn't make it very performant.
So I moved on to my second idea: local cursor variables. The problem with
this is that if I create a local cursor variable by executing a dynamic
query, I can't extract it from the EXEC (or sp_executesql) context, as
it offers no output variable.
I guess my concrete questions are:
Is it possible to execute a dynamic sql query and extract a (cursor) variable from it?Is it possible to populate a local cursor variable with a global cursor, by providing the global cursor's name?Can I create a local cursor variable for a dynamic sql query? How?
Anybody sees another way arround this?Thanks in advance,
Carlos
Snehalata writes "does view for vertical partitioning improves the performance, since the view will have all the columns which exist in the original table(without partitioning?"
View 1 Replies View RelatedHi
is it possible to set the vertical line to stretch from top of list box to the bottom even If the list box might grow at run time I want the line should stretch from top to bottom
We can merge two horizontal cells but how to merge two table cells vertically, Means R1C1 with R2C1 to make R12C1?
Thanks lot...
I am having trouble in writing code to save the SQL query output to an XML file.
A simple example like below is working fine and it creates an output a.xml.
------Begin Code-----
Declare @fNameTemp varchar(50)
Declare @SQL varchar(3000)
Set @fNameTemp = 'c:XMLDumpa.xml'
Set @SQL = 'bcp "Select top 10 * From test..month For XML Auto, Elements" QueryOut "' + @fNameTemp + '" -c -t, -T -S'
Exec master..xp_cmdshell @SQL
------End Code ------
But I am having trouble in writing when I change the Select query to a more complex one as in the example below. The code is part of SP.
------Begin Code-----
USE [test]
GO
Declare @TblName1 varchar(10)
Declare @TblName2 varchar(10)
Declare @District varchar(6)
Declare @Month varchar(3)
Declare @TblType varchar(10)
Declare @Thisyear varchar(10)
Declare @Lastyear varchar(10)
SET @TblName1 = '2006exp'
SET @TblName2 = '2005exp'
SET @District = 1
SET @Month = 2
SET @TblType = 'exp1'
set @Thisyear = '2006'
set @Lastyear = '2005'
set @TblName1 = '[' + @TblName1 + ']'
set @TblName2 = '[' + @TblName2 + ']'
set @TblType = '[' + @TblType + ']'
Declare @fNameTemp varchar(50)
SET @fNameTemp = 'C:XMLDump2.xml'
Declare @SQuery nvarchar(3000)
SET @SQuery = 'bcp "Select d.descrip_1 as [Commodity Description], ty.HS4, ty.Amount1 as ['+ @ThisYear +' Value ($)], (ty.Amount1/ty.Total1)*100 as ['+ @ThisYear +' Share (%)] ,
py.Amount2 as ['+ @LastYear +' Value ($)], (py.Amount2/py.Total2)*100 as ['+ @LastYear +' Share (%)]
FROM
(Select top 10 a.commodity1 as HS4, Sum(a.all_val_mo) as Amount1,
(select Sum(a1.all_val_mo) FROM test..' + @TblName1 + ' a1 where a1.stat_month <=' + @Month + ' and a1.district=' + @District +' ) as Total1
FROM test..' + @TblName1 + ' a where a.stat_month <=' + @Month + ' and a.district=' + @District +'
Group by a.commodity1 Order by Amount1 DESC) ty
JOIN
(Select b.commodity1 as HS4, Sum(b.all_val_mo) as Amount2,
(select Sum(b1.all_val_mo) FROM test..' + @TblName2 + ' b1 where b1.stat_month <=' + @Month + ' and b1.district=' + @District +' ) as Total2
FROM test..' + @TblName2 + ' b where b.stat_month <=' + @Month + ' and b.district=' + @District +'
Group by b.commodity1) py on ty.HS4=py.HS4 LEFT OUTER JOIN '+ @TblType +' d on ty.HS4=d.commodity1
Order by Amount1 DESC FOR XML AUTO, Elements" QueryOut "' + @fNameTemp + '" -c -t, -T -S'
Exec master..xp_cmdshell @SQuery
------End Code ------
Output for above code is shown below:
usage: bcp {dbtable | query} {in | out | queryout | format} datafile
[-m maxerrors] [-f formatfile] [-e errfile]
[-F firstrow] [-L lastrow] [-b batchsize]
[-n native type] [-c character type] [-w wide character type]
[-N keep non-text native] [-V file format version] [-q quoted identifier]
[-C code page specifier] [-t field terminator] [-r row terminator]
[-i inputfile] [-o outfile] [-a packetsize]
[-S server name] [-U username] [-P password]
[-T trusted connection] [-v version] [-R regional enable]
[-k keep null values] [-E keep identity values]
[-h "load hints"] [-x generate xml format file]
NULL
I don't know what is the mistake I am doing. I would appreciate if anyone can help me figure the error.
http://www.fiu.edu/~atmakurk/ustrade/SQL_XML1.jpg
The above query works perfectly if I remove BCP & For XML Auto part. Here is a screen shot of Successful query results.
http://fiu.edu/~atmakurk/ustrade/working_example.jpg
I'm working on a couple projects and I've recently been trying to make everything fully normalized so updates are easier and I'm just wondering if there's a standard way to query and update normalized tables.
For example:
If I have table People with columns ID, FirstName, LastName, Height, Weight, ShoeSize, I can normalize that into two tables. Table People has ID, FirstName and LastName. Table PeopleDetails has PeopleID (FK), Property and Value. That way i can add more properties later right at the presentation layer if I like. Essentially I moved the data from being horizontal to being vertical.
But doing a simple search for people means I have to search the details table and return a LOT more records (one each for Height, Weight and ShoeSize) not to mention any more details I might add later. With a lot of details, it seems like your performance would take a big hit and your code would get really complicated as your looping through a vertical dataset to find the properties you want. Or is there some other standard way of doing that?
I'm just hoping that someone else has solved these problems and there's a standard set of functions out there for selecting and updating this kind of DB structure. Anyone?
Following is my table and its content:YearTargetedBudgetFirstQuarterSecondQuarterThirdQuarter-----------------------------------------------------------------------------------------------------20002500012000110001000200135000220002100020002002450003200031000300020035500042000410004000I want a query which returns the result in this format:Yr_Col1Yr_Col2Yr_Col3Yr_Col4----------------------------------------------------20002001200220032500035000450005500012000220003200042000110002100031000410001000200030004000There could be many work around, but what would be fasted single queryfor this?
View 8 Replies View RelatedHi,
Does anyone know how to format a text in a textbox or a table cell to show vertically?
Or, rotating a textbox with 90 degree angle? Thanks.
Hi All,
I setup a Transactional replication between SQL Server 2000 and SQL Server 2005 via
wide area network.
The intial snapshoot failed because run into problems on a table with column 379 columns.
How do ceate Vertical Partioning a table on Publication?
Thank you.
TJ_1
I am using ReportViewer Control on my asp.net page. Report consists of three datasets with each having its own table. All thses three tables are vertcally and horizonatlly aligned and have same numbers of columns. Report looks like a homgeneous single report. Output of report is on three pages since there are many rows. Page 1 and 2 displayes rows from dataset1/table1(it has large amout of data compared to other two datasets) and last page has remaining rows from dataset1/table and and rows from dataset2/table2 and dataset3/table3. While on page 1 and 2 vertical scrollbar runs fine but on page 3 vertical scrollbar crashes as soon as one tries to scroll, with follwoing
errror message:Microsoft JScript runtime error: 'children.0.children.0.children.1.children' is null or not an object. Horizontal scrollbar runs ok without any problme. While in preview mode and after deploying and viewing on server it does not crash. It crashes only while viewing on webpage in asp.net application.
By setting FixedHeader property to false the crashing behaviour is stopped.
But now how to achieve FixedHeader without crashing is question.
Any help to solve this problem will be highly appricated and I would like to thank you in advance.
Thank you.
Bharat Gadhia