Display Row Data As Cols
Jun 6, 2006
I have data in rows that I need to aggregate and display in a columnar fashion and I haven't been able to figure out how to do it. A simplified version of the data as it is stored in the table:
Station Month Day Reading
1 1 1 100
1 1 2 200
1 1 3 300
1 2 1 400
1 2 2 500
And I would like to create a query that returns:
Station Month Day 1 Day 2 Day 3
1 1 100 200 300
1 2 400 500
Any help you can provide or tips to steer me in the right direction are much appreciated.
Dianne Siebold
View 6 Replies
ADVERTISEMENT
Jul 28, 2007
Hello ,
I need to make the automatic subtotal column in most right position in a matrix wider than the data columns , i faild so far and discovered that its width is proportional to the data column width , its logic for the total column to be wider since its value is sum(all left data columns)
any way around this ?
thanks
Bassam
View 1 Replies
View Related
Apr 30, 2015
I would like to display a portion of report where there is data or no data
There is data subreport display
Product Name Latex Gloves
Product ID
xxxx5678
There NO data in the subReport
Product Name
Product ID
View 3 Replies
View Related
Oct 5, 2006
I have a XML column in my source DB. Is there any way possible using SSIS to convert the XML to ordinaty cols (varchar) in the destination db.
I am using Sql 2k5.
thanks in advance
View 1 Replies
View Related
Jun 27, 2007
my table
id; part_name ; part_desc
01; xpto ; descr
02; qwerty ; azerty
03; azerty ; descr xpto
I want to search from two columns
something similar to :
SELECT * FROM tools WHERE (part_name LIKE @parameter_search or part_desc LIKE @parameter_search);
If i search for "xpto" only the first line would show.
How can i solve/go around this?
View 4 Replies
View Related
Aug 30, 2005
Hi
There is a table which has repeated values in more than one col.
I want to view all the distinct values for both the cols
for eg
col1 col2 col3
A B D
A B E
B C E
B C F
I want the output as
col1 col2
A B
B C
cheers
Vic
Vicky
View 8 Replies
View Related
Jan 12, 2002
I'd like to know if I can make one proc/cursor that I can pass only a table name and it will determine what cols exist in the table?
AND, what is the syntax for such a query?
I will be receiving data back where I will receive the accountID and only the deltas will have values all other columns will be null.
My proc will update an existing record, updating the specific col when an accountID exists, it will create a new record if the accountID does not exist.
I'd like to search all the cols getting their names and values when not null.
I can construct a proc for each table searching each col by known name.
However, I'd like to know if I can make one proc/cursor that I can pass only a table name and it will determine the cols?
All my tables start with cols AccountID and end with RecID with any number of cols inbetween.
TIA
JeffP...
cross posted to ms.pub.mssqlserver.programming
View 2 Replies
View Related
Jul 14, 2006
Hi,
How can we use two identity column (with identity property) in table ?
GK
gk
View 5 Replies
View Related
Jun 5, 2007
I have about 30 tabs with same struture.
I want to simple put column 2 in all 30 table in a new table without joining on anything. Is there a way to do that?
Thanks
JP
View 3 Replies
View Related
Oct 24, 2004
Hello,
I have a table with 1 column containing numeric values, and I need to create a query which displays the count of values for each set of conditions. For example, if the table contains the values 1,2,3,4,5 and 6, the output of the query should look like this:
L M H
-----------------------
3 2 1
(L are values below 4, M between 4 and 5, H is 6 and above)
Any suggestions?
TIA
View 1 Replies
View Related
Jun 12, 2006
For ex.
Table Match_List ( MatchID, UserID_A, UserID_B)
constraints like
MatchID primary key
UserID_A <> UserID_B
unqiue index (UserID_A,UserID_B)
but I wish to exclude duplicated rows like 1,1,2 & 2,2,1,
cause UserA to UserB, and UserB to UserA are the same thing.
How can I get this?
View 7 Replies
View Related
Oct 12, 2006
Hi I have a table which has couple of name cols and 10 varchar columns. The requirement was to retain the first two cols but convert the 10 cols into a single XMl.
the table has this schema;
ManagerName varchar
UserName varchar
TextField1 Varchar
TextField2 Varchar
TextField3 Varchar
TextField4 Varchar
TextField5 Varchar
TextField6 Varchar
TextField7 Varchar
TextField8 Varchar
TextField9 Varchar
TextField10 Varchar
I wrote a query like;
SELECT
TPD.[NameId]
,CrtdUser.[UserId]
from [Olddb_DB] TPD
join
[NewDB]..[Manager]CrtdUser
on
Crtduser.managerName = TPD.ManagerName
join
[NewDB]..[Users]Users
on
Users.[loginDetail] = TPD.[UserName]
to get the cols other than the XML
/*********/
SELECT
XmlDat.[textField1],
XmlDat.[textField2],
XmlDat.[textField3],
XmlDat.[textField4],
XmlDat.[textField5],
XmlDat.[textField6],
XmlDat.[textField7],
XmlDat.[textField8],
XmlDat.[textField9],
XmlDat.[textField10],
XmlDat.[textField11],
XmlDat.[textField12]
from
[olddb_db]..[DETAIL_DEF] XmlDat,[NEWDB]..[Detail_Def]TPD
where TPD.Name = XmlDat.Name
for XML AUTO, ELEMENTS
and this query to convert all the varchar cols to a XML. Now the problem is I am not sure of how to combine both these queries to Insert into the new table.
Can someone guide me in this
the destination table looks like
Name (Varchar)
UserId (int)
UserVal_XMl (XML)
Thanks in advance
View 3 Replies
View Related
Jul 3, 2006
Hi,
With reference to http://www.intelligententerprise.com/001020/celko.jhtml?_requestid=235427
I want the "sql stmt" which wud give the lft and rgt col values..
i am reading his book but cant understand :eek: where he explains
wat lft and rgt cols are..
"The root is always (lft,rgt) (1, 2*(Select count(*)from table) and leaft nodes are (lft+1=rgt)" :S
View 3 Replies
View Related
Jun 8, 2007
CREATE TABLE [RS_A] ([ColA] [varchar] (10)[ColB] [int] NULL)CREATE TABLE [RS_B] ([ColA] [varchar] (10)[ColB] [int] NULL)INSERT INTO RS_AVALUES ('hemingway' , 1)INSERT INTO RS_AVALUES ('vidal' , 2)INSERT INTO RS_AVALUES ('dickens' , 3)INSERT INTO RS_AVALUES ('rushdie' , 4)INSERT INTO RS_BVALUES ('hemingway' , 1)INSERT INTO RS_BVALUES ('vidal' , 2)I need to find all the rows in A which do not exist in Bby matching on both ColA and ColBso the output should bedickens 3rushdie 4So if i write a query like this , I dont get the right result setSELECT A.ColA, A.ColBFROMRS_A AINNERJOIN RS_B BONA.ColA <B.ColAORB.ColB <B.ColBBut if i do the following, i do get the right result, but followingseems convoluted.SELECT A.ColA, A.ColBFROMRS_A AWHERE ColA + CAST(ColB AS VARCHAR)NOT IN (SELECT ColA+CAST(ColB AS VARCHAR) FROMRS_B B)
View 6 Replies
View Related
Jul 9, 2007
I am trying to display hirearchical data using data region. For eg. the first level will be a list of customer names. The second level will be a list of Invoices under each customer. The third level will be a list of product names that were sold under each invoice.
I used a list for the data region and in each hireachy, there is a textbox in each list to display the data. I am trying to associate each data region with a datatable (I will provide the logic to populate the datatable with appropiate data). I do not want the ReportViewer to interact directly with a database because this is a 3 tier design and I am using the ReportViewer in local mode. I couldn't find any example of these. Can anybody help? Also I would like to see an example of the .rdlc file in these situation. Thanks very much.
Kam
View 4 Replies
View Related
Oct 10, 2006
I've always bound data to a dataset...how do I display data without a dataSet? This is how i usually display data... SqlConnection myConnection = new SqlConnection(conn); string sqlQuery = "SELECT * FROM questions"; SqlDataAdapter myCommand = new SqlDataAdapter(sqlQuery, myConnection); DataSet ds = new DataSet(); myCommand.Fill(ds, "questions"); MyDataGrid.DataSource = ds.Tables["questions"].DefaultView; MyDataGrid.DataBind(); <%# DataBinder.Eval(Container.DataItem, "answer3")%>
View 1 Replies
View Related
May 26, 2006
Hi All
I am new to VS 2005 and ASP.NET. I used to use Dreamweaver to design but now I am trying VS.
What I want to do is to display data retrieved with a SqlDataSource in a web page. I know how to do it by binding it to the various controls (Grid, DataList, DetailsView, FormView, Repeater.) available but how can I display it without using any of the mentioned (Grid, DataList, DetailsView, FormView, Repeater)?
Any help apprecited.
View 1 Replies
View Related
Apr 2, 2001
Hi,
I have stored procedure with cursor
I have to use following
step 1 fetch RateValidDates into @Id_RateCode, @TheValidDate
.... get data
step 2 fetch prior from RateValidDates
... condition
step 3 fetch NEXT FROM RateValidDates into @Id_RateCode, @TheValidDate
... go to next record
Sql query analyzer output result of fetch NEXT FROM on the screen
What seeting should i use to avoid output on screen
(The reason My cursor takes about 8000 - 10000 rows when it's open)
View 1 Replies
View Related
Dec 22, 1999
When I select a table and try to view the data I take the return all rows or return top option. However nothing is displayed. These options worked before and I don't understand why it's not working now. Can anyone help?
View 1 Replies
View Related
Jan 28, 2015
I want to output this result. how to write this query? "This is new amount 1000" select "This is new amount "+@sum(amt) as amount from table
View 2 Replies
View Related
Sep 28, 2005
I have a db; that stores a company name; if the end user types in
The Stoneware Jar Company
I would like to display:
Stoneware Jar Company
All company names entered like that with no preceding "The"
How can I display something diff on my ASP Page? Possible?
View 20 Replies
View Related
Apr 4, 2007
Hi,
I have a result set from a store procedure which looks like this.
Column1 Column2 Column3
A 1 lit1
A 1 Rep2
A 1 Reg3
B 2 ram1
B 2 lim2
I want to show data in my table like this:
Column1 Column2 Column3
A 1 lit1; Rep2;Reg3
B 2 ram1;lim2
Can anyone tell me how I can do this?
Thanks
Ashwini
View 1 Replies
View Related
Oct 30, 2007
I am using a filter in a matrix control. I would like to display a "No data" type message if no data is present. The problem I'm running into is the base query is returning data, but the optional filter is removing all rows from display.
So using CountRows in the base dataset doesn't quite cut it. Is there an alternative?
View 4 Replies
View Related
May 6, 2007
Hello,
I have matrix object in reportin (SQL 2005 reporting services)
When in some field have no data it display NaN.
How can I change it ?
I try to use iif(my function = nothing.....) but didn't help
View 1 Replies
View Related
Jan 6, 2008
I have a fairly complicated report consisting of numerous datasets and numerous tables.
In one of my tables I want to display the message "No data to display" if nothing comes back from the query. Currrently the table just disappears if there is no data. Is there a way to display the table if there is nothing in the query?
View 4 Replies
View Related
Mar 5, 2006
Hi all. i have the following function below,which use to retrieve the order detail from 2 table which are order detail and product. i have many duplicated order id in order detail, and each order id has a unique product id which link to product to display the product information. however when i run the following function below . its duplicated each product info and dsplay in the combo box. May i know whats wrong with my code?
Public Sub ProductShow()
Dim myReader As SqlCeDataReader
Dim mySqlCommand As SqlCeCommand
Dim myCommandBehavior As New CommandBehavior
Try
connLocal.Open()
mySqlCommand = New SqlCeCommand
mySqlCommand = connLocal.CreateCommand
mySqlCommand.CommandText = "SELECT * FROM Product P,orders O,orderdetail OD WHERE OD.O_Id='" & [Global].O_Id & "' AND P.P_Id=OD.P_Id "
myCommandBehavior = CommandBehavior.CloseConnection
myReader = mySqlCommand.ExecuteReader(myCommandBehavior)
While (myReader.Read())
cboProductPurchased.Items.Add(myReader("P_Name").ToString())
End While
myReader.Close()
Catch ex As Exception
MsgBox(ex.ToString)
Finally
connLocal.Close()
End Try
End Sub
View 1 Replies
View Related
Jul 23, 2007
I have the following sqlDataSource. I wan't to display one piece of data, not a whole row, from it. I can find lots of information on putting it into a datagrid, but nothing on puttin one piece in a textbox. I cannot use a formview as we are embedding html in response.write and it breaks in a formview. Thanks.
<asp:SqlDataSource runat="server"
ID="myEmpInfo"
SelectCommand="Select di.EmpInfo,di.eth,gc.t_level From tblEmp di
,tblMisc gc
Where di.empnum = @empnumand di.empnum = gc.empnum" DataSourceMode="DataReader" ConnectionString="<%$ ConnectionStrings : myConnectionString %>">
<SelectParameters>
<asp:QueryStringParameter Name="empnum" QueryStringField="empnum"/>
</SelectParameters>
</asp:SqlDataSource>
View 1 Replies
View Related
Dec 12, 2007
Hi,
I have a requirement where I need to display the items category wise
I have 3 tables,
one stores category_id, parent_id and category_name
second table stored item_id,item_name and proce
3rd table stores category_id and item_id
I need to display the values like this
Category name, Item name
with first parent category ans ites items should be displayed and next sub category and its item
By category table with contain 2 levels of sub category
i.e Category1
Category11
Category111
any help with the query would be much appreciated, I wrote the query something like this....
select c.category_name,parent_id,item_name from category c,items i,Category_item ci where
ci.item_id=i.item_id and c.category_id=ci.category_id order by parent_id, category_nameBut it will display all parent category items then the sub category items I want to display like thiscategory1 items1category11 item11category1 item12category11 item111category11 item112 category2 item2
Thanks
View 2 Replies
View Related
Mar 19, 2008
I have a webform that I am wanting to display data from a database. Right now I am using the gridview but for future purposes when it comes to maintenance what would be the easiest way to do updates? For example, if I add a new record into the database I would like the webform to automatically update itself to show all the records including the new record I added. Do I need to use a "table" and somehow connect it to a database? Do any stored procedures need to be created? Suggestions/ideas/codes help help help would be verrrry much appreciated!!! Also I am using MS Server 2003 and C# as the programming language. Thank you!!!!
View 9 Replies
View Related
Mar 28, 2008
I have a tabe in my sql server 2005 database which contains a long description and I want to display the first 100 charactors on my web page but not sure how to do this I can only display the whole description. I'm not sure if I need to write the query to the database so it only brings the first 100 charactors back or query the whole description and then take the first 100 charactors in asp.net and display that can any one help
View 3 Replies
View Related
Apr 25, 2008
Good Day, Please help me, I have a database that looks like this, Building No. Floor No. Room No. Customer Name Appliances Appliances Codea 1 01 John TV 12a 1 02 //up tob 20 10 james Ref. 22b 20 10 joe Microwave 15 //and soon so if you would notice some rooms are vacant, as it usually happens, the buildings, floors and rooms are fixed except for the customers and the appliances. so at first i made this gridview and display this data in this manner:Customer Location (Building + floor) total rooms Used Rooms Available rooms A1 2 1 1 B2010 2 2 0 so if you would look at the table above, it just SUM up all tenants in a specific Location then Group it by Customer location,but i need to add the appliances in a different format like this: Customer Location (Building + floor) total rooms Used Rooms Available rooms TV Ref Microwave
A1
2 1 1 tick
B2010
2 2 0 tick tick so this is what i need to show, i already have done the first table (w'o appliances) but how can i make this second table?? please help me, SALAMAT PO.,Thanks
View 3 Replies
View Related
Dec 5, 2006
Hi!
I have data in the following manner:
period course1 course2
4 NULL Eng
4 NULL Math
4 Phys NULL
4 Chem NULL
Is there any way I can show this data this way (the order is irrelevant - row1 can be joined with row3 or row4)?
period course1 course2
4 Phys Eng
4 Chem Math
4 Phys Eng
4 Chem Math
Basically, I want to be able to remove nulls.
Please let me know if any of you have come across such an issue.
Thanks so much!
-Parul
View 3 Replies
View Related
Oct 21, 2014
I have a table. I have some sensitive data that I don't want to display that sensitive data. The columns I want to abstract are
Firstname varchar(50) not null
Latname varchar(50) not null
dob datetime not null
addressline1 varchar(50) null
adressline2 varchar(50) null
city varchar(50) null
postalcode varchar(20) null
I want to display null values as nulls empty as empty not null as numbers.
View 4 Replies
View Related
