Make Tbl_SP From Vertical To Horizontal

Jul 7, 2004

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

View 1 Replies


ADVERTISEMENT

Make A Horizontal Table Into A Vertical Table

Nov 19, 2004

Hi 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

View 6 Replies View Related

Horizontal To Vertical Array?

Nov 29, 2012

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,

View 5 Replies View Related

How To Convert Vertical To Horizontal Row

Jun 13, 2008

id name
--- -----------------
236 SERVICE REQUEST
236 HARDWARE
236 Desktop
336 Loan

id name
-- ----------------------------
236 SERVICE REQUEST/ HARDWARE/ Desktop/Laptop/ Loan

View 5 Replies View Related

Horizontal To Vertical Table

Jan 7, 2015

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...".

View 2 Replies View Related

Sql For Results From Vertical To Horizontal

Mar 15, 2008

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

View 2 Replies View Related

Converting Vertical Data To Horizontal

May 25, 2006

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

View 4 Replies View Related

Append Vertical Row Result Into Horizontal

May 19, 2015

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]....

View 14 Replies View Related

Challenge : Vertical View To Horizontal In A StorProc

Jul 9, 2004

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"

View 5 Replies View Related

Have Query Results Display Vertical Instead Of Horizontal?

Apr 15, 2015

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.

View 5 Replies View Related

T-SQL (SS2K8) :: Horizontal To Vertical Rows Conversion?

May 14, 2014

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

View 1 Replies View Related

Horizontal Output Of Vertical Query With Cursors

Mar 31, 2008

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

View 1 Replies View Related

How To Convert Data Horizontal To Vertical In Server

Jul 24, 2015

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] ....

View 2 Replies View Related

Textbox Rotation Or Display Horizontal Vs Vertical

Jun 20, 2006

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.

View 37 Replies View Related

Textbox Rotation Or Display Horizontal Vs Vertical (and WRAPPING)

Mar 26, 2008



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!!

View 1 Replies View Related

When To Make A Vertical Partition In Data Warehous

Jun 5, 2008

Hi All,

I am trying to understand, when would I do a vertical partition in a Dimensional Data Warehouse ? What are the things I need to consider, before I take the decision?

Necessity is the mother of all inventions!

View 7 Replies View Related

Could Anybody Tell Me How To Convert Vertical Data Into Horizontal Data?

Sep 2, 2005

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 Related

Vertical Partitioning

Jan 4, 2007

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 Related

Vertical Line

Dec 17, 2006

Hi
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

View 5 Replies View Related

Vertical Merging

Aug 6, 2007

We can merge two horizontal cells but how to merge two table cells vertically, Means R1C1 with R2C1 to make R12C1?

Thanks lot...

View 1 Replies View Related

Using A Normalized (vertical) Table

Sep 24, 2007

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?

View 6 Replies View Related

Horizotal To Vertical Display

Sep 29, 2005

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 Related

How To Create Vertical Text?

Jan 13, 2006

Hi,

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.

 

View 3 Replies View Related

How To Create Vertical Partioning

Feb 13, 2007

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

View 4 Replies View Related

Vertical Scrollbar Crash

Jul 6, 2007

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

View 10 Replies View Related

How Do I ... Loop Horizontal??

Feb 3, 2005

If I have the raw data dumped into a big table as following:

Date P R M E Date P R M E Date P R M E Date P R M E
1/1/90 1 2 3 4 1/1/90 2 3 4 5 1/1/90 3 4 5 6 1/1/90 4 5 6 7
...
1/1/05 1 2 3 4 1/1/05 2 3 4 5 1/1/05 3 4 5 6 1/1/05 4 5 6 7

And this table has a repeating block [D, P, R, M,E] 300 times. Is it possible to write a loop query/stored procedures/triggers (or whatever it is) to read each repeating block and stack them on top of each other to insert into another table which has the same structure as following?

Look like this?
Date P R M E
1/1/90 1 2 3 4
...
1/1/05 1 2 3 4
1/1/90 2 3 4 5
...
1/1/05 2 3 4 5
1/1/90 3 4 5 6
...
1/1/05 3 4 5 6

If there is a solution would you please elaborate, example?
Thank you for the help.
shiparsons

View 14 Replies View Related

Horizontal Positioning

Jul 20, 2005

Hello all,Thinking about building a new database in the enterprise addition ofsql server and using some horizontal parititioning techniques in orderto accomaodat what will eventually be a monster huge database.Can you share some hard earned experience, gotchas, etc...with me? Wewill be setting up this server on a SAN array that will be made up ofjust one or two huge virtual RAID10 volumes and I am also wonderingabout the wisdom of this? Its simple and should work, but is thatconfig relevant in any way specifically to our plan to partition?

View 2 Replies View Related

What Does Mean By Horizontal Partitioning ?

Mar 7, 2008

Hello frnds......what does mean by Horizontal Partitioning ?

View 4 Replies View Related

What Dos Mean By Horizontal Portioning ?

Mar 7, 2008

Sorry for the earliear question as itself is wrong ?

This is a right questions and what does it mean by ?

View 5 Replies View Related

Horizontal Results

Jul 8, 2006

how can i use concatenation to query the results horizontally?
example output:
CustomerID=1002, Name=Mr Anderson, City=NY

View 10 Replies View Related

Select Data By Vertical Dimension

Mar 12, 2007

Dear everyone,
i have a table like below:
id title
1 a
2 b
3 c
and i want to get data from the table above with this format:
column1 column2 column3 column4
1               a          2         b
3               c

How can i do this with select statement or inner join?? or an posible way , please help me

Thnks

View 1 Replies View Related

Select Data By Vertical Dimension

Mar 12, 2007

Dear everyone,
i have a table like below:
id title
1 a
2 b
3 c
and i want to get data from the table above with this format:
column1 column2 column3 column4
1               a          2          b
3               c

How can i do this with select statement or inner join?? or an posible way , please help me

Thnks

View 3 Replies View Related

Vertical Page Breaks In 2000?

May 15, 2007

Hi,



I'm using Rpt Svc in SQL 2000. I have a report that uses a table data region. Is there a way to control placement of vertical page breaks? As in when the page is just too wide to print on a single sheet of paper.



Thanks.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved