Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server






SuperbHosting.net & Arvixe.com have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for BigResource.com.







Using Row Data To Create New Columns


i have been trying to create new columns in a tabinvoice details using row data from another table and additional input from another form. Problem is i get no sql errors m database conection is ok usually works but doesn't write any new columns or values to the tblinvoicedetails here is the code i am using i hope someone ca tell me where i am going wrong.



<table width="100%" cellspacing="0" cellpadding="2" border="0">
<form name="form2" method="post" action="index.php" onsubmit="return v.exec()">
<?
$cid = mysql_real_escape_string($_GET['cid']);
$query = "select * from tblcustomerdetails where intCustomerID = '$cid'";
$result = mysql_query($query);
$row = mysql_fetch_array($result);
$cid = $row["intCustomerID"];
?>
<tr>
<td><?print_r( $row['vcCCompanyName'])?></td><td align="left">Tel: <?print_r( $row['vcCDPhone'])?></td>
</tr>
<tr>
<td><?print_r( $row['vcCStreetNo'])?> <?print_r( $row['vcCStreet'])?></td><td align="left">Fax: <?print_r( $row['vcCFaxNo'])?></td>
</tr>
<tr>
<td><?print_r( $row['vcCCity'])?></td><td align="left">Email: <?print_r( $row['vcCEmail'])?></td>
</tr>
<tr>
<td><?print_r( $row['vcCState'])?></td><td> </td>
</tr>
<tr>
<td><?print_r( $row['vcCPostalCode'])?></td><td> </td>
</tr>
<tr>
<td><?print_r( $row['vcCCountry'])?></td><td> </td>
</tr>
</table>
<br />
<table width="100%" cellspacing="0" cellpadding="2" border="0">
<tr>
<td> </td><td><b>Code</b></td><td><b>Name</b></td><td><b>Description</b></td><td><b>Price</b></td><td colspan="2" align="center"><b>Action</b></td>
</tr>
<?
$accountsql="select * from tblpricedetails where intCustomerID=$cid order by intProductID asc";
$tempdata=$DB_site->query($accountsql);
$count =$DB_site->num_rows($tempdata);
while($rowdata=$DB_site->fetch_array($tempdata))
{
$intPriceID = $row["intPriceID"];
$intProductID = $row["intProductID"];
$vcProductCode = $row["vcProductCode"];
$vcProductName = $row["vcProductName"];
$vcProductDescription = $row["vcProductDescription"];

?>
<tr>
<td class="accountsumb">
<?=$rowdata["intProductID"]?>
</td>
<td class="accountsume">
<?=$rowdata["vcProductCode"]?>
</td>
<td class="accountsumc">
<?=$rowdata["vcProductName"]?>
</td>
<td class="accountsumc">
<?=$rowdata["vcProductDescription"]?>
</td>
<td class="accountsumc">
<?=number_format($rowdata["ftAmount"],2,".",",");?>
</td>
<td class="accountsumc" align="right">
<input type="text" size="10" name="Quantity" />
</td>
<td class="accountsumc" align="center">
<input type="text" size="10" name="total" />
</td>
</tr>
<?
}
?>
<? if($count==0)
{
?>
<tr bgcolor="#EFEFEF" class="smallblack">
<td colspan="7" align="center" bgcolor="#EFEFEF" class="redtext">This user has no product data. </td>
</tr>
<?
$insertdate = mysql_query("INSERT INTO tblinvoicedetails (intProductID,vcProductCode,vcProductName,vcProductDescription,ftAmount) VALUES(".$row['intProductID'].",".$row['vcProductCode'].",".$row['vcProductName'].",".$row['vcProductDescription'].",".$row['ftAmount'].")") or die(mysql_error());

}

?>

<tr>
<td colspan="7" align="center"><br />
<input name="submit" type="submit" class="button" value="Submit" />
</td>
</tr>
<tr>
<td colspan="7" align="center">
<input class="button" type="reset" name="reset" value="Reset" />
</td>
</tr>
</table>
</form>


View Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
A Word About Meta-data, Pass Through Columns And Derived Columns
Here's another one of my bitchfest about stuff which annoy the *** out of me in SSIS (and no such problems in DTS):

Do you ever wonder how easy it was to set up text file to db transform in DTS - I had no problems at all. In SSIS - 1 spent half a day trying to figure out how to get proper column data types for text file - OF Course MS was brilliant enough to add "Suggest Types" feature to text file connection manager - BUT guess what - it sample ONLY 1000 rows - so I tried to change that number to 50000 and clicked ok - BUT ms changed it to 1000 without me noticing it - SO NO WONDER later on some of datatypes did not match. And boy what a fun it is to change the source columns after you have created a few transforms.

This s**hit just breaks... So a word about Derived Columns - pretty useful feature heh? ITs not f***ing useful if it DELETES SOME of the Code itself after there have been changes in dataflow. I cant say how pissed off im about that SSIS went ahead and deleted columns from flow & messed up derived columns just because the lineageIDs dont match.

Meta-data - it would be useful if you could change it and refresh it - im just sick and tired of it that it shows warnings and errors when there's nothing wrong - so after a change i need to doubleclick all my transforms so that those red & yellow boxes would disappear.

Oh and y I passionately dislike Derived columns - so you create new fields based on some data - you do some stuff - combine multiple columns to one, but you have no way saying remove the columns from the pipeline. Y you need it - well if you have 50K + rows with 30+ columns then its EXTRA useless memory overhead for your package.

Hopefully one day I will understand how SSIS works (not an ez task I say) - I might be able to spend more time on development and less time on my bitchfest - UNTIL then --> Another Day - Another Hassle with SSIS

View Replies !   View Related
When Then To Create Mult Columns
I have a colm day and colm sales, I would like to have a column for each day of week i think i can use when then but not sure how

View Replies !   View Related
Create Array Of Columns
Absolute beginner to SQL here...

I'm try to create an array of columns and then refer to these columns at the asp.net level.

Can't seem to find this in the SQL literature...so I'm probably looking at this all wrong.

Thanks in advance.

Larry

View Replies !   View Related
How To Create Rule Which Update Other Columns
Hi,I have a table with the following columns:ID INTEGEDR,Name VARCHAR(32),Surname VARCHAR(32),GroupID INTEGER,SubGroupOneID INTEGER,SubGroupTwoID INTEGERHow can I create a rule/default/check which update SubGroupOneID &SubGroupTwoID columns when GroupID for example is equal 15 onMSSQL2000.It is imposible to make changes on client, so I need to checkinserted/updated value of GroupID column and automaticly updateSubGroupOneID & SubGroupTwoID columns.Sincerely,Rustam Bogubaev

View Replies !   View Related
Could Not Create A Foreign Key On 2 Unique Columns
Hello.
Could anyone tell me why it is not possible to create a foreign key on two columns those references on 2 columns in another table? Those 2 columns have each a unique constraint.

I have:
CREATE TABLE T_PK (ID1 INT CONSTRAINT CHK_UNIQUE1 UNIQUE,ID2 INT CONSTRAINT CHK_UNIQUE2 UNIQUE)

CREATE TABLE T_FK (ID1 INT, ID2 INT)

And I want to do:

ALTER TABLE T_FK ADD CONSTRAINT CHK_FK FOREIGN KEY (ID1, ID2) REFERENCES T_PK (ID1,ID2)

I see no reason why this is not working because always
a row in the table T_FK referencing only one row in table T_PK.

Thank you.
Have a nice day.

View Replies !   View Related
How To Create Dynamic Columns In Sql Database
hi,
in the front end i have two fields one is dropdown list another is checboxlist say dropdownlist consist of title name and checkbox list consists of sub title name.on selecting title name from dropdownlist i will be getting respective title name in checkboxlist. here when i select checkboxlist(suppose if there are 6 items in checkboxlist n if i select only 3)then my databse column should generate one dropdownlist selected item and 3 checkboxlist selected item.

Eg: if dropdownlist contains( title1,title2,title3) and checkboxlist contains(chk1,chk2,chk3,chk4,chk5,chk6). if i select title1 from dropdownlist i will be geeting 6 item in checkbox list) if i select only 3 items of checkbox list. then i should be able to create column name by title1,chk1,chk2,chk3


help is appreciated

View Replies !   View Related
How To Dynamically Create Columns For A Table
Hi,
how to dynamically create columns for a table

View Replies !   View Related
Avoid Typing To Create Columns
I need to create table in sql server 2005 database, I have plenty of columns. Well I know how to create them manually. but does any body know or tried before to create columns automatically, or through some batch process. I have column names already typed in TXT file (comma delimited).

all I am trying to avoid that typing work.

any advice.

Thanks

View Replies !   View Related
Is There A Way To Create Columns From A Row Value In A Select Statement?


Hi!

I have a select question that look like this:


DECLARE @ID uniqueidentifier;

SET @ID = '40bd3052-60f4-414a-99df-ca882c128493';


SELECT

rp.ID AS ReportPackId

,rp.SupplierPartyIdentifier AS SupplierPartyIdentifier

,rpap.AdditionalPartyIdentifier AS AdditionalPartyIdentifier

FROM

ReportPack rp

INNER JOIN ReportPackAdditionalParty rpap

ON rpap.fk_ReportPack = rp.ID

WHERE rp.ID = @ID

The result a get when running the select question is:








ReportPackId
SupplierPartyIdentifier
AdditionalPartyIdentifier

40BD3052-60F4-414A-99DF-CA882C128493
String
addPartyId1

40BD3052-60F4-414A-99DF-CA882C128493
String
addPartyId2

My problem is that the result I want is the following:









ReportPackId
SupplierPartyIdentifier
AdditionalPartyIdentifier
AdditionalPartyIdentifier

40BD3052-60F4-414A-99DF-CA882C128493
String
addPartyId1
addPartyId2

I always know that the ReportPackId and SupplierPartyIdentifier will be identical for all rows because of the Where condition, and therefore I want all AdditionalPartyIdentifiers to be in columns instead of a new row. Is this possible?

View Replies !   View Related
Create Table From Text File, Extract Data, Create New Table From Extracted Data....
Hello all,

Please help....

I have a text file which needs to be created into a table (let's call it DataFile table). For now I'm just doing the manual DTS to import the txt into SQL server to create the table, which works. But here's my problem....

I need to extract data from DataFile table, here's my query:

select * from dbo.DataFile
where DF_SC_Case_Nbr not like '0000%';

Then I need to create a new table for the extracted data, let's call it ExtractedDataFile. But I don't know how to create a new table and insert the data I selected above into the new one.

Also, can the extraction and the creation of new table be done in just one stored procedure? or is there any other way of doing all this (including the importation of the text file)?

Any help would be highly appreciated.

Thanks in advance.

View Replies !   View Related
Create Unique Index On Multiple Columns
Hello, I will explain myself further. I want to make my table in such a way that no two colums have the same value for example:
Row 1 - Column 1 = "cool"
Row 1 - Column 3 = 91
Row 3 - Column 1 = "cool"
Row 3 - Column 3 = 91
 
I dont care about one column having duplicate values, I want to protect against  Column 1 and 3 having the same values on other rows. Is this possible to do in sql server?

View Replies !   View Related
How To Create A Unique Constraint On Composite Columns
I am trying to create a Unique Constraint on a SQL Server 2005 table where the uniqueness is based on 2 columns.

Could anybody provided some help on how I could enforce this on an existing table (link, or example) I have been looking around without luck.

Thanks in advance

John.

View Replies !   View Related
Create Table With Veriable Number Of Columns


Hi everyone,

I need to create temporary table in one of the SP.The problem is that number of columns in table will vary depanding on input in SP.

How can i create table with variable number of columns?

Thanks,


Alex

View Replies !   View Related
Is It Possible To Dynamically Create Columns In A Table In SSRS


Hi,
I have a sproc that returns somevalues and everything is working fine... and in my reports i am assigning theheader data(in a detail column) based on the some feilds in the sproc... and there around 20 feilds that i want to show... but at a given time i am pretty sure that there wont be more than 10 fields that will have data.

So is it possible that show only the columns that have data in it and sometimes if there is less that 5 - 6 fields.. i want to realign the widths in those tables..

any help is appreciated..
Regards
Karen




View Replies !   View Related
How To Create Dynamic Columns In A Temporary Table
Hi there,
i have a requirement that a temporary table contains dynamic columns depending on where condition.

my actual table is like





Key
Value


X1
x

X3
x

X5
x

Y1
y

Y2
y
when user select x, the input variable passed to stored proc and the result is shown like

column names
X1 X3 X5 as column headers.

the select query is from temporary table.

these out put is based on the user selection. so the temporary table created with columns dynamically.

please help me out.
please let me know if you didn't understand.

thanks
Praveen.

View Replies !   View Related
When To Create Columns And Metadata For Custom Asynchronous Component Output
I'm having a tad bit of trouble getting output from an asynchronous component that I've written and am looking for some insight.

This component takes in a name string passed from upstream and parses the name components into standardized output fields. I'm using an asynchronous component because if the name string contains two names ("Fred & Wilma Flintstone") I'm outputting one row for Fred and one for Wilma. I've gotten it to run and with debugging have observed what appeared to me to be proper execution, but zero rows are flowing out of it.

In my ProvideComponentProperties method, I add the three fields and there associated metadata to the OutputColumnCollection. Is this method where this should occur? It's before the PrimeOutput method, so I didn't know if I should be creating the output columns in ProcessInput (i.e., after the output buffer is provided by PrimeOutput.)

In ProcessInput, I'm using AddRow for each input row and another if it contains a second name, setting the value for each index using the buffer's SetString method, to no avail. I can observe it to this point, but then don't know what's in that output buffer (if I'm using the wrong buffer index value, etc)

Thanks.

View Replies !   View Related
Create Output Columns Based On Input In Custom Component


I'm trying to create a fairly simple custom transform component (because I've read that's the easiest one to create) which will take one column from a flat file source and based on the first row create the output columns.
I'm actually trying to write a component that will solve the now well known problem with parsing CSV files in SSIS. I have a lot of source files and all have many columns so a component that can read in the first line from the CSV file and create the output columns automatically will save me lots of time when migrating the old DTS packages.

I have the basic component set up but I'm stuck when trying to override the OnInputPathAttached method because I don't know how to use the inputID to get the first line from the input (the buffer).
Are there any good examples for creating output columns dynamically based on the input buffer?
Should I just give up on on the transform and create a custom source component instead?


View Replies !   View Related
How Create Multiple Driildowns For All Columns In Table Using Ssrs Report


hai, iam new to ssrs please help me,

my report having 10 columns, i can explain my need with example that is,assume like thiscustomer id is first column of my table customer id is 101 it having 3 departments a1,b1,c1,perticular department that is a1 having emp1,b1 having emp2,c1 having emp 3.

i want output like this when clicking + customer id driildwon it display 3 departments taht is a1,b1,c1, when +a1 drill down clicking i need to dispaly emp1, corresponding b1 to emp2 , c1 to emp 3.

above explanation is only one column of the table, like that iam also displaying this driiling procedure for remaining different columns.

and i need to display customer information by weekly,daily,monthly,yearly at bottom of the report


please give which logic used in creatingformat like abovedrilldown report which having multiple drilldowns for all columns in a table


if any body give procedure for creating fromat for drilldown report which having multiple drilldowns for all columns in a table is appriciate.


thanks


jacks



View Replies !   View Related
Import Data From Excel-Sheet Via OleDb In VB.Net - How To Get A Columns Data As String?
Hello,

i want to import data from an excel sheet into a database. While reading from the excel sheet OleDb automatically guesses the Datatype of each column. My Problem is the first A Column which contains ~240 Lines. 210 Lines are Numbers, the latter 30 do contain strings. When i use this code:







Code BlockDim sConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & conf_path_current & file_to_import & ";Extended Properties=""Excel 8.0;HDR=NO"""
Dim oConn As New OleDb.OleDbConnection(sConn)
Dim cmd1 As New System.Data.OleDb.OleDbCommand("Select * From [Table$]", oConn)
Dim rdr As OleDb.OleDbDataReader = cmd1.ExecuteReader
Do While rdr.Read()
Console.WriteLine(rdr.Item(0)) 'or rdr(0).ToString
Next




it will continue to read the stuff till the String-Lines are coming.
when using Item(0), it just crashes for trying to convert a DBNull to a String, when using rdr(0).ToString() it just gives me no value.

So my question is how to tell OleDB that i want that column to be completly read as String/Varchar?

Thanks for Reading

- Pierre from Berlin


[seems i got redirected into the wrong forum, please move into the correct one]

View Replies !   View Related
RS2k Issue: PDF Exporting Report With Hidden Columns, Stretches Visible Columns And Misplaces Columns On Spanned Page
Hello:

I am running into an issue with RS2k PDF export.

Case: Exporting Report to PDF/Printing/TIFF
Report: Contains 1 table with 19 Columns. 1 column is static, the other 18 are visible at the users descretion. Report when printed/exported to pdf spans 2 pages naturally, 16 on the first page,3 on the second,and the column widths have been adjusted to provide a perfect page span .

User A elects to hide two of the columns, and show the rest. The report complies and the viewable version is perfect, the excel export is perfect.. the PDF exporton the first page causes every fith column, starting with the last column that was hidden to be expanded to take up additional width. On the spanned page, it renders thefirst column on that page correctly, then there is a white space gap equal to the width of the hidden columns and then the rest of the cells show with the last column expanded to take up the same width that the original 2 columns were going to take up, plus its width.

We have tried several different settings to see if it helps this issue or makes it worse. So far cangrow/canshrink/keep together have made no impact.It is not possible to increase the page size due to limited page size selection availablility for the client. There are far too many combinations of what the user can elect to show or hide to put together different tables to show and hide on the same report to remove this effect.

Any help or suggestion on this issue would be appreciated

View Replies !   View Related
Data Flow: Converting Data In Multiple Columns
Hi,

I'm just wondering what's the best approach in Data Flow to convert the following input file format:

Date, Code1, Value1, Code2, Value2

1-Jan-2006, abc1, 20.00, xyz3, 35.00

2-Jan-2006, abc1, 30.00, xyz5, 6.30

into the following output format (to be loaded into a SQL DB):

Date, Code, Value

1-Jan-2006, abc1, 20.00

1-Jan-2006, xyz3, 35.00

2-Jan-2006, abc1, 30.00

2-Jan-2006, xyz5, 6.30

I'm quite new to SSIS, so, I would appreciatedetailed stepsif possible. Thanks.



View Replies !   View Related
Create Query To Average Data & Total/sum Data
Hello,
I am very new to SQL and just getting to learn this stuff. To make this question easier I will scale down the fields dramatically.

I have about 8000 records close to 2000 records for the last 4 years
and I would like to create a query that will create a table on my SQL server. I need to bind the data based on two items the Year and the Name and average several records. However, one record needs it's own calculation.

Here are my field names:
[year] ***4 choices 2007, 2006, 2005, 2004***
[name]
[rush_no] ***integer***
[rush_net] ***integer***
[YPC] *** This field needs to be calculated by [rush_net] divided by [rush_no]***decimal***

I also need to create the same table that will "total/sum" the same records.

View Replies !   View Related
Approach Help To Load Data From Flatfiles Into Relational Table Where Data Is Coming As Spaces In Few Columns From Flatfiles
Hi ,

My Input is a flat file source and it has spaces in few columns in the data. These columns are linked to another table as a foreign key and when i try loading them in a relational structure Foreigh key violation is occuring , is there a standard method to replace these spaces.

what approach should i take so that data gets loaded in a relational structure.

for example

Name Age Salary Address
dsds 23 fghghgh

Salary description level
2345 nnncncn 4

here salary is used in this example , the datatype is char in real scenario

what approach should i take to load the data in with cleansing the spaces in ssis

View Replies !   View Related
How To Create A Data Warehouse Or Data Mart
i want to create a data mart from an existing OLTP database. for example northwind or i will create an OLTP database. i dont know how i can create data mart from OLTP database. i want to learn that step by step. help me??? please!!

View Replies !   View Related
Sum Data In Two Columns
Hi All

Please can someone help me
I Have a problem whereby I cannot get the contents of two columns to add together

my code is
update RN_20000718115255CounterParty1set[RN_20000718115255CounterParty1].[19] =
([RN_20000718115255CounterParty1].[19]) + ([RN_20000718115255CounterParty1].[17])
could someone please advise where i have gone wrong

thanks & regards

View Replies !   View Related
Cant Add New Columns To Data Tables
Hi All,I am having  a problem for which i dont find any reasons ..I hope to get a solution from here.I have 2 tables ..1 with around 150 columns and the other with around 80 columns.I have view based on these tables.The problem i m facing from yesterday is that i cant add /delete/change on these tables.If i make a change on these tables and hit the save button the sql server enterprise just hangs..i also tried to add the columns through the query but no results.I cant even drop this view.Please any help on this??Thanks

View Replies !   View Related
Testing Columns For Data
Can someone advise me as to the best way to loop through a collection of columns for each row in a table testing the value of each column for nulls etc and then entering the column name into another column where the condition is true..

View Replies !   View Related
Combine Data Into One Row, Add Columns
Hey guys,

I realize I've posted something like this before, but i'm confused how to do something new, what I am trying to do is combine rows of data when certain columns equal one another.

For example, I have to sets of data who have in common the columns ctp_code and a mod_code (they are the same) however they have two other columns called billing_amt and amount_owed that need to be added together (literally taking the data and doing the math, so 100.00 + 100.00 = 200.00)


Code:

id ctp_code mod_code billing_amt amount_owed
1 1 1 100.00 100.00
2 1 1 100.00 100.00



My results should be this:


Code:

id ctp_code mod_code billing_amt amount_owed
1 1 1 200.00 200.00



Any help, thoughts?

View Replies !   View Related
Query: Two Columns With Same Data
Hi,
I have 3 tables:
Table SLA: SLA_code, SLA_name
Table industry: Industry_code, Industry_name
Table14: SLA_code_origin, SLA_code_destination, Industry_code, Freq

I would like to query to get in one table:
SLA_name for DESTINATION, Industry_name, Freq

Note: SLA_code contains the same codes as SLA_code_origin and SLA_code_destination. All are the same codes in column oringin means the From point and colum destination the To point.

How can I query to get the Freq and Industry for those destination SLAs?

I have make a relationship between:
Table SLA.SLA_code with Table14.SLA_code_destination and
Table_industry.Industry_code with Table14.Industry_code

But I am not sure if just a
SELECT SLA.SLA_name, Industry.Industry_name, Table14.Freq
FROM SLA, Industry, Table14
would select the SLA_destination and not the SLA_origin.

Thanks very much in advance,

Maria

View Replies !   View Related
Data From Different Columns For One Column
Hi,

I have a tabel called "Daily" which has 5 columns, "TesterID", "Activity", "Hours Given", "Hours Used", "Delta".
The data for "TesterID" and "Hours Given" columns are taken from a table called "Tester".
Data for columns "Activity" and "Hours Used" taken from table called ALD.
"Delta" column is the difference between Hours Given and Hours Used.
For "Hours Given" in table "Daily", the data source should change every 12 hours.
For Monday-Friday Mornings, "Hours Given" should read data from "Tester.Weekdays_day" and for Monday-Friday Nights it should read data from "Tester.Weekdays_Night" and for Saturday-Sunday Mornings it should read from "Tester.Weekend_Day" and for Saturday-Sunday Nights it should read from "Tester.Weekdays_Night" .

How to do that? Please Help. Thank you.

View Replies !   View Related
Combining Data From Two Columns
Basically what I am doing is uploading inventory information from our point of sale/inventory management software into our website. I have a third party app that does most of the work for me but I need a view of our item table in our POS that this 3rd party software will use as the data source for what gets uploaded into the web. I have created the view with the following sql statement:
CREATE VIEW dbo.vwsfwebitemswine
AS
SELECT dbo.Item.*, dbo.Category.Name AS CategoryName, dbo.Department.Name AS DepartmentName
FROM dbo.Item INNER JOIN
dbo.Category ON dbo.Item.CategoryID = dbo.Category.ID INNER JOIN
dbo.Department ON dbo.Item.DepartmentID = dbo.Department.ID
WHERE (dbo.Item.DepartmentID = 7) AND (dbo.Item.WebItem = 1)


As you can see I am pulling from 3 tables. Item, Category and Department so that I can get the actual category and department names not just their numeric ID codes referenced in the standalone item table. The view data looks like this:

UPC ….. CategoryName DepartmentName
12345….. MerlotWine
34556….. ChadonnayWine
Etc…

What I need is an additional column created that combines the DepartmentName column values with the CategoryName column values and includes a ~ in between like this.

UPC ….. CategoryName DepartmentName Combined
12345….. MerlotWine Wine~Merlot
34556….. ChardonnayWineWine~Chardonnay
Etc…

I’m not a SQL expert by any stretch. I’ve gotten this far using this forum but I’m stuck. Any help would be greatly appreciated.

View Replies !   View Related
Select Data From Top N Columns
Suppose you have table with many columns and often you need data from first 15 or 20 columns. In that case you have to specify all the columns in your select statement. This procedure will select top N columns you want. All you have to do is to supply table name and number of columns you want

Here is the procedure



Create procedure TopNcolumns (@tableName varchar(100),@n int)
as
Declare @s varchar(2000)
set @s=''
If exists(Select * from information_Schema.tables where table_name=@tablename and table_type='Base Table')
Begin
If @n>=0
Begin
set rowcount @n
Select @s=@s+','+ column_name from information_schema.columns
where table_name=@tablename order by ordinal_position
Set rowcount 0
Set @s=substring(@s,2,len(@s)-1)
Exec('Select '+@s+' from '+@tablename)
End
else
Select 'Negative values are not allowed' as Error
End
else
Select 'Table '+@tableName+' does not exist' as Error



Madhivanan

Failing to plan is Planning to fail

View Replies !   View Related
Split The Data Into Columns
I have a table calledproducts with the values like

ProductId ProductName
10 A
20 D,E,F,G
30 B,C
40 H,I,J

I need to display each productid's with

ProductId ProductName
10A

20 D
20 E
20 F
20 G
30 B
30 C
40 H
40 I
40 J

I will be appreciated if you cansend me the code.

Thanks,
Mears


View Replies !   View Related
Adding Data To Columns
Hi

I'm am using SQL Server 2005 Express on my home PC.

To practice some of the SQL skills I learned on a course I was recently on, I am trying to build a mini database which I can run some queries from.

I have created some tables and added columns to these tables. However, I cannot find how to add data to these columns.

I'm sure it's pretty simple but I just can't find it! Can anyone help??



Cheers!









View Replies !   View Related
Find Out Which Table Columns Data Comes From
TableName: EmployeeCloumns:     EmployeeID                  EmployeeName                  If I do "Select * From Employee", is there a way to do something like this...
Loop through each result   IterateThroughControls.ID &= dbTable & dbColumnName & "<br />"EndSo that the end result would assign ID values...EmployeeEmployeeIDEmployeeEmployeeName

View Replies !   View Related
Need A Way To Switch Specific Data From Columns
Basically I have 635k records in a table with a person's first name, and date of birth (other stuff but it's not relavent). I imported all the data from excel files, but somehow a bunch of records got the first name and date of birth mixed up, so I'm trying to write a stored procedure that would switch the first name with the date of birth wherever the firstname is purely numeric, or something of the sort. Now records are in fact repeated so another possible but more time taking solution is to write a stored procedure that I give the date of birth and it does the switching around for the respective date of birth when it's found inside the First name. Any suggestions? All the code I've written has proved useless :/

View Replies !   View Related
Loading Data Into Columns 75 - N In Tables?
I'm having a very irritating time trying to migrate data from a COBOL system to SQL Server.

One of the A/R Master files has approx. 200 columns.

I can export this file any number of ways that will (sometimes) load partially into my database, but always when the load succeeds, columns 75 through N simply contain NULL, even though there is data in the file. When the load fails in DTS, the error is always missing column delimiter. Using BULK INSERT the error is always something like data too long at column 75.

Putting all this together, I have deduced that something isn't working if I try
to load a staging table with more than 74 columns of data. This seems like a way-too-low threshold for a robust database, especially since SQL Server is supposed to be able to handle up to 1,024 columns per table.

Has anyone ever encountered this problem?

Thanks in advance for any help

View Replies !   View Related
Restore Deleted Columns With Data
I need to restore a single table which one of my developers has deleted two columns from that table with all datas.

The copy of that database is on another server ,if some one can show me through EM, a step by step i will appreciate.

Thank you.

View Replies !   View Related
Transact SQL - Combining Two Columns Of Data
How do I combine two columns (ex: [first name] [last name]) to get a combined result in a new column of ([first name] [last name]).

Thank you,

Andrew

View Replies !   View Related
Concatenating Columns Of Different Data Types
We have a table in DB2, which has a column1 (Data type: - decimal (10, 0)) and column2 (Data type: - varchar (70)), I need to concatenate these two columns into one while displaying output

Procedure I followed:--

Tried to convert column1 to varchar using cast function so that I could concatenate both columns. However I ended up in getting the following error message

DB2 SQL error: SQLCODE: -461, SQLSTATE: 42846, SQLERRMC: SYSIBM.DECIMAL; SYSIBM.VARCHAR

Message: A value with data type "SYSIBM.DECIMAL" cannot be CAST to type "SYSIBM.VARCHAR".

Please do let me know where I went wrong

Thanks
Pratibha Surya

View Replies !   View Related
Hw To Convert Row Data Into Multiple Columns??
Hello,

Table - 1 Format:-

Line_No Entity_Id Mid
1157530
2157540
3157550
4157560
5157570
6157580
7157590
81575100

Now i need the data into below Format :-

Line_NoEntity_Id Ticker Mid1Mid2 Mid3 Mid4 Mid5 Mid6 Mid7 Mid8
11575 COFP 30 40 50 60 70 80 90 100

Can any one help me out?

Thanks
Prashant Hirani

View Replies !   View Related
Want To Convert Columns Data Into Rows...
Hello All,

I have one table with 9 different columns for e-mail address like mail_address1, mail_address2 etc....

now i want to insert serial no 1 for mail_address1, serial no 2 for mail_address2 like wise for a particular id.

Current Format :-

code mail_address1 mail_address2 mail_address3
1111 abc@yahoo.com xyz@yahoo.com null

Required format:-
code sr_no mail_address
1111 1 abc@yahoo.com
1111 2 xyz@yahoo.com
1111 3 null

i hope that i am clear with my question.

Can any one help me?

Thanks
Prashant

View Replies !   View Related
Data Flows And Unique Columns.
Perhaps one too many 2000 DTS packages have permanently damaged my ability to think clearly - however, I've find myself very frustrated attempting to create a SSIS Data Flow which replaces a very simple 2000 DTS package.

Take data from table1 in database1, put it in table2 in database2. Table2 in Database2 has an additional column as part of the primary key - so I need to add an arbitrary unique value in each row as it's inserted. Previously, I did this in the transformation script through a variable I incremented.

What's the recommend method to dothis now - since row level processing of variables seem to be a no-no?

View Replies !   View Related
Preview Has Data But No Columns Returned
In my SSIS package, I connect to an externalSQL server database. This external database supports a stored procedure that I need to execute to "retrieve data". So in my package, I set the DataAccess Mode property of my OLEDB datasource to "SQL Command" and I provide the command EXEC <proc_name> <Param>,<output_param>. (The proc has an output parameter). The preview shows all the columns and data, but somehow no columns are returned....so when I try to link this data source to a copy column task, I get an error saying the source does not have any columns...any idea why this could be happening. Thanks - Manmeet

View Replies !   View Related
Removing Columns From A Data Flow
Seems obvious but I can't see how. How would I remove columns from a data flow so that columns which have been used earlier but are not needed for insert/update are taken out of the flow.

I'm asking because the data ends up in a update statement and the flow has got so big it is unreadable.

Cheers, Al

View Replies !   View Related
Moving Rows Data Into Columns ..
Hi guys,
I have a table with the following structure
ID CodeIDCodeDesc
1 10AA AAAAAAA
2 20BB BBBBBBBB
3 30CCCCCCCCCC
4 10DDDDDDDDDD
5 20EE EEEEEEEEEE

Now I need to create a new table with the following structure

CodeID AA BB CC DD EE
10 AAAAAAAANull Null DDDDDD Null
20 Null BBBBBBBBNull Null EEEEEEEEE
30 Null Null CCCCCCCC Null Null

I have managed to create a logic that solve this problem by selecting all AA values and insert it to the new table and then using a series of update statements I updated the values of BB, CC, DD The problem is that I have to do a 30 update statement so the performance is very bad.
Are there any better solution to implement this table using SSIS that could give me a better performance ?

Thanks a lot.

View Replies !   View Related
Data Spanning Onto Multiple Columns


I am creating a report that contains a table. The table only has one column containing 10characters so there is alot of wasted space on the page. I would like the columns to wrap to at leasttwo columns on each page. I have tried to set the number of columns on the report properties to two, however it seems as if the second column is disabled and the data does not wrap into the second column when the report is run.

Any suggestions would be GREATFULLY appreciated!

Thank You
Kaysie

View Replies !   View Related
Filling Up Missing Columns Data


Hi,

I have the following table :




Code Block
Create table #Customers (custidint,name nvarchar(20),age int, st_code int,
citycode int, internalid int,type int,oldcustid int)

Insert into #Customers values(101,'Aron',23,1,1,12,1,0);
Insert into #Customers values(102,'Cathy',28,1,1,13,1,0);
Insert into #Customers values(103,'Zarog',33,1,1,14,1,0);
Insert into #Customers values(104,'Michale',25,1,2,12,1,0);
Insert into #Customers values(105,'Linda',43,1,2,13,1,0);





Insert into #Customers values(106,'Burt',53,1,2,14,1,0);


If you observe, the rows are unique based on the internalid per st_code,per city_code

Problem :
Now the user inserts another row but this time he passes only the following :

Insert into #Customer values(120,'AronNew',null,1,1,12,null,null) -
Note he doesnt pass the age or the type

I want that when he passes this row, i match up this row with the existing row based on st_code,citycode and internalid and then update the new row with the missing values(only columns with null)that were there in the existing row

OUTPUT

120,'AronNew',23,1,1,12,1,0

How could I write the query for this?

thanks.

View Replies !   View Related
Swap The Data Between 2 Columns Of A Table In Sql
I have a table named tbOrders. I want to swap the data between 2 of its columns named OrderNumber and CaseNumber.

Both the columns are varchar datatype. How can i swap the data between the two columns?

View Replies !   View Related

Copyright 2005-08 www.BigResource.com, All rights reserved