Make A View With Column Names From Row Values
May 22, 2008
Hi,
I have a table of results for various measured quantites and i need to turn this into a view. Only problem is i need to seperate the measured quantities and their respective values into seperate columns.
At the moment I have something like:
Quantity : Value
--------------------
Quantity 1 : 0.12
Quantity 1 : 0.56
Quantity 2 : 2.36
Quantity 2 : 5.34
Quantity 2 : 4.13
Quantity 3 : 10
Quantity 3 : 15
and I need a view that looks like:
Quantity 1 : Quantity 2 : Quantity 3
-------------------------------------
0.12 : 2.36 : 10
0.56 : 5.34 : 15
null(?) : 4.13 : null(?)
I've tried using pivots but they don't seem to help
Any ideas?
View 7 Replies
ADVERTISEMENT
Apr 28, 2008
I need to create the following table in reporting services
PRODUCT April March Feb
2008 2007 2008 2007 2008 2007
chair 8 9 7 4 4 4
table 3 4 5 6 4 6
My problem is the month names are a column in the dataset, but I dont know how to get it to fill as column headers???
Thanks in advance!!!
View 1 Replies
View Related
Feb 10, 2007
In SQL 2005 - when I display the results of a View and Copy all rows and columns, the resulting Paste in Excel does not include the column names.
How can I set SQL 2005 so the names of the columns will come along with the content of the copy function?
Background:
When I am using a SQL Query (instead of a view) I have the ability to control whether or not I am able to Include the Column Headers when copying or saving results. The control exists in the Options > Query Results > Results to Grid > Include column headings etc.
My question is how to get this same ability when attempting to copy the results of a VIEW vs. a Query.
Thank you,
Poppa Mike
View 3 Replies
View Related
Aug 31, 2007
I have consistent column names that load into a grid view. I now need to change the names in the grid view programatically. I was originally trying to get cute with SQL to do this, but I've been told my below solution will not work and I'm better off to do this in the presentation layer. If this is true, remember I'm still wet behind the ears here...how do I do this in vs2005?
Here's my post to the SQL devs to give you an idea what I'm trying to do.
I have a query that grabs fields from a denormalized table. The result is column names Week1, Week2, Week3....Week26. Users want to see the actual date instead of Week#. So I have a table (lkpdatecaptions ) that contains the fields "fldfieldno" and "Fldcaption". fldfieldno Fldcaption----------- --------------11 9/07/200712 9/14/200713 9/21/2007So fieldno 11 represent week1 and so on. So my hope is to update the alias with a query like: Select fldcaption from forecast.tlkpdatecaptionswhere fldfieldno = 11That quey returns the value of 9/7/2007 and is the value I need to represent the coumn alias name.My current query looks like this:SELECT SUM(forecast.tblforecastdenormalized.fldwk01) AS WEEK1,So can I do something like the following?SELECT SUM(forecast.tblforecastdenormalized.fldwk01) AS (Select forecast.tlkpdatecaptions.fldcaption from forecast.tlkpdatecaptions where fldfieldno = 11), ...
View 6 Replies
View Related
Mar 14, 2007
Hi,I'm trying to create views on all my existing tables and for that I'dlike to create a script or so.I don't want to specify the '*' for the columns in the create viewstatement. I prefer to specify the column names.I have the column names int sys.columns table but Do not know how tohandle them to have a statement like that:CREATE VIEW myVIEWWITH SCHEMABINDINGASSELECT col1name, col2name, col3name, etc...from sys.columns....?????.....Anyone can help?thx,Chris
View 1 Replies
View Related
Oct 19, 2006
I want to pass user-defined column names to views or procedures
I am using SQL Server 2005 Express.
I tried this.
CREATE PROCEDURE @userDefinedColumn varChar(10) = 'My_Column'
select @userDefinedColumn, count(ID) as [Total Records] from My_Table
GROUP BY @userDefinedColumn
This was not accepted. It won't allow a user defined parameter in the group by.
But I need to pass user defined parameters to views as well.
View 5 Replies
View Related
Jan 8, 2007
When I am using a SQL Query I have an ability to control whether or not I am able to Include the Column Headers when copying or saving results.
The control exists in the Options > Query Results > Results to Grid > Include column headings etc.
My question is how to get this same ability when attempting to copy the results of a VIEW vs. a Query.
The idea is that when I setup a view it€™s a drag/drop type of query building (query building for dummies if you will). Once I have a view and click the Execute icon it will return all the records selected by the View. However, when I click the upper left/top box to select all rows and column and then try to copy/paste the records into Excel all the data copies just fine but the field name/column headers are not there.
How can I get the header fieldname date to copy/paste from View result set that I'm able to copy from a Query result set?
Thank you,
Mike
View 1 Replies
View Related
May 20, 2014
We have 2 tables (table a and b)
select a.* from table a inner join table b
on a.col1<> b.col2
I would like to have column names where the values are not matching.
View 4 Replies
View Related
Nov 4, 2015
I have a field in a table that contains a different formula (varchar(1000)) for each record. It's along the lines of something like this, although each formula is different: ([ColumnA] - [ColumnB])/([ColumnC] - [ColumnD]). I plug that into a dynamic SQL statement so that it can get executed in a select statement.
Due to the variations of the formulas, checking for Divide by Zero, etc, we want to move this to a .NET method. We'd like to replace "ColumnA" and "ColumnB", etc., with the actual values so that we're passing something like (5-3)/(6-2). I haven't been able to figure out a way to do this without actually executing it. We don't want to pass the solution, but the equation filled with the actual values rather than the column names.
View 3 Replies
View Related
Apr 17, 2008
I am building reports against our TFS development db.
One of the reports tracks days spent (Dwell Time) in various status categories (eg: New, Assigned, In Development, Hold, etc) for a given "ticket".
For a fixed list of values from {Work Item].System_State, I can send the results (days in Assigned) to the column named (Assigned) for each status for each event in the [Work Item History], and then sum them for each ticket as:
Ticket ID New Assigned InDev etc
1230001 2 0 0 ...
1230001 0 1 2 ....
SUM 2 1 2 ....
However, I have many different Projects, each of which use their own Status names.
I don't want to duplicate the same basic report, if I can avoid it.
How can I name and generate this data for the unique Status list for each Project?
Simplest analog is: name = First(Fields!Status.Value, "TFSdb")
and allows value for a column name (category) as: =IIF(Fields!Status.Value = First(Fields!Status.Value, "TFSdb"), Fields!Days.Value, 0)
However this fails beause:
1. It only delivers the FIRST status value, and,
2. I cannot SUM an expression which is itself an aggregate (using First).
View 4 Replies
View Related
Sep 26, 2007
Hi,
I have a table as follows
Table Master
{
Id varchar(20),
Cat1 datetime,
Cat2 datetime,
Cat3 datetime,
Cat4 datetime
}
and the data in the table is as follows
Table Master
{
Id cat1 cat2 cat3 cat4
-----------------------------------------------
1 d11 null d13 d14
2 d21 d22 d23 d24
3 NULL d32 d33 d34
4 d41 d42 NULL NULL
}
I want to retrive column names and its values wheb the ID matches to some value.
Can any one please let me know how to do this?
Thanks alot
~Mohan
View 3 Replies
View Related
Jul 31, 2007
The table tA contains (here I m using storedprocedure to return the values as columns varchar(8000))
ID LocationName
1 Door
2 FrontCounter
The number of records may vary like sometimes it have 2 or 3 or 4 or more records
So I have to return row values as column name.
tAChild
POSID storeID tAID Auth
1 140 1 true
1 140 2 false
Result Should be like ( to create this I m using SP with Dynamic sql query to create temp table)
POSID storeID Door Window
1 140 true true
if more records in tA it should append in the end of result as new column...
Is this the above is right way to implement ?any other ideas about this...
Please suggest me......
View 4 Replies
View Related
Apr 18, 2008
Hi.
In my report, I need to display column names (and its values) from my dataset, which uses the stored procedure and within it use the PIVOT statement. I cannot use the matrix control in report since I need to add another static column to the right of the matrix, but since that cannot be done ni SQL SSRS 2005, I need to PIVOT it through stored procedure. The parameter passed to stored procedure is the year and it is used for pivot. The statement looks like this:
@sql = 'SELECT * FROM (SELECT YearId, SchoolId, ReportText, OutputValue_Text from AggregateTable) AS AGAM
PIVOT (MIN(OutputValue_Text) FOR YearId IN ([' + @YearId + '])) PVT'
EXEC(@sql)
So, since the years passed as parameters can change, I cannot hard-code it as column in my report, so I need to programatically display column names and values from my dataset, something like dataSet.Tables[0].Columns[].ColumnName; I have tried with writing the code, but I do not have much experience with it and I have not been very successful. Can someone help me with the code?
Thanks
View 2 Replies
View Related
Jun 10, 2015
I created a query that got the following result. But I expect to get the structure like, care_nbr, cust_nbr,legal_name, address_type=physical address, addr_line_1, addr_line_2, address_type-primary address, ddr_line_1, addr_line_2. That means I only need primary and physical address, and expect them to show in a row to each care_nbr. How to perform that?
CARE_Nbr||Cust_Nbr||Legal_Name||||||| Address_Tpye |||Addr_Line_1 ||||||||||||||||Addr_Line_2
99000001||004554||Mac Marketing, LLC||Billing Address||210 Parktowne Blvd Ste. 1||NULL
99000001||004554||Mac Marketing, LLC||Mailing Address||210 Parktowne Blvd Ste. 1||NULL
99000001||004554||Mac Marketing, LLC||Primary Address||210 Parktowne Blvd Ste. 1||NULL
99000001||004554||Mac Marketing, LLC||Physical Address||210 Parktowne Blvd Suite 1||NULL
My script is here, and the sample result is attached. How should I modify this query to get my expected result?
select a.CARE_Number,
a.Customer_Nbr_Txt,
a.Customer_Type_Txt,
a.Legal_Name_Txt,
c.Address_Type_Txt,c.Address_Line_1_Txt,c.Address_Line_2_Txt,
a.Customer_Status_Txt,
a.Legal_Entity_Type_Txt,
a.Business_Unit_Txt
FROM dw_mart.dbo.DimCustomer a
[code]....
View 1 Replies
View Related
Sep 5, 2004
I need to check whether procedure found any matches or not. If not it has to return the column name where matching value was not found. For example, if there was no record found in the table "Addresses" column "customer" with the value @username, it should return "street". If id with value @prod_id was not found in the table "Products", the "productname" must be returned as well.
CREATE PROC sp_test
@id INT,
@username VARCHAR(50),
@prod_id INT
AS
SELECT name FROM Customers WHERE id=@id
SELECT street FROM Addresses WHERE customer=@username
SELECT productname FROM Products WHERE id=@prod_id
It is kind of check, which has to find out if users have inserted all the necessary values or not.
Thanks for any advice.
View 4 Replies
View Related
Jun 10, 2015
I created a query that got the following result. But I expect to get the structure like, care_nbr, cust_nbr,legal_name, address_type=physical address, addr_line_1, addr_line_2, address_type-primary address, ddr_line_1, addr_line_2. That means I only need primary and physical address, and expect them to show in a row to each care_nbr. How to perform that?
CARE_Nbr||Cust_Nbr||Legal_Name||||||| Address_Tpye |||Addr_Line_1 ||||||||||||||||Addr_Line_2
99000001||004554||Mac Marketing, LLC||Billing Address||210 Parktowne Blvd Ste. 1||NULL
99000001||004554||Mac Marketing, LLC||Mailing Address||210 Parktowne Blvd Ste. 1||NULL
99000001||004554||Mac Marketing, LLC||Primary Address||210 Parktowne Blvd Ste. 1||NULL
99000001||004554||Mac Marketing, LLC||Physical Address||210 Parktowne Blvd Suite 1||NULL
How should I modify this query to get my expected result?
select a.CARE_Number,
a.Customer_Nbr_Txt,
a.Customer_Type_Txt,
a.Legal_Name_Txt,
c.Address_Type_Txt,c.Address_Line_1_Txt,c.Address_Line_2_Txt,
[code]....
View 6 Replies
View Related
Jan 21, 2014
How do I add column names as Total and SubTotal for NULL values.
SELECT DISTINCT
--[Group]
[Month]
,[Market]
,[Environment]
,[type]
, COUNT(*)
[code]....
View 9 Replies
View Related
Jan 22, 2004
Hi
I was wondering if anyone has an idea of how we could find the table names and column names of the tables in our Sql server database at runtime/dynamically given our connection string? Please let me know.
Thanks.
View 5 Replies
View Related
Jul 29, 2010
When I run the parse command in SSMS, it merely does a syntax check. When I run through the export data wizard by right clicking on a table, it allows a query as the data source. When I click on the parse button in the window that accepts that query, it resolves object names and notifies me of invalid ones. I'd really like the same thing to happen when I parse in SSMS...
View 5 Replies
View Related
Sep 7, 2015
We have SharePoint list which has, say, two columns. Column A and Column B.
Column A can have three values - red, blue & green.
Column B can have four values - pen, marker, pencil & highlighter.
A typical view of list can be:
Column A - Column B
red  - pen
red - pencil
red - highlighter
blue - marker
blue - pencil
green - pen
green - highlighter
red  - pen
blue - pencil
blue - highlighter
blue - pencil
We are looking to create a report from SharePoint List using SSRS which has following view:
          red   blue  green
  pen       2    0    1
  marker    0    1    0
  pencil      1    3    0
  highlighter  1    1    1Â
We tried Sum but not able to display in single row.
View 2 Replies
View Related
Sep 3, 2015
I have an SSIS package that imports data from an Excel file, replaces any value in Excel that reads "NULL" to "", then writes the data to a couple of databases.
What I have discovered today, is I have two columns of dates, an admit date and discharge date column, and what I need to do is anywhere I have a null value in the discharge date column, I have to replace it with the value in the admit date column.Â
I have searched around online and tried a few things using the Replace funtion in Derived columns but no dice so far.Â
View 3 Replies
View Related
Jul 23, 2005
I am looking to create a constraint on a table that allows multiplenulls but all non-nulls must be unique.I found the following scripthttp://www.windowsitpro.com/Files/0.../Listing_01.txtthat works fine, but the following lineCREATE UNIQUE CLUSTERED INDEX idx1 ON v_multinulls(a)appears to use indexed views. I have run this on a version of SQLStandard edition and this line works fine. I was of the understandingthat you could only create indexed views on SQL Enterprise Edition?
View 3 Replies
View Related
Feb 12, 2014
I want to add $ symbol to column values and convert the column values to western number system
Column values
Dollar
4255
25454
467834
Expected Output:
$ 4,255
$ 25,454
$ 467,834
My Query:
select ID, MAX(Date) Date, SUM(Cost) Dollars, MAX(Funded) Funding from Application
COST is the int datatype and needs to be changed.
View 2 Replies
View Related
Apr 17, 2008
I create a user then I create a database and assign my newly created user as owner
When I login as this new user everything works fine BUT my newly created user can still see other database names even though he is not allowed to use them.
How can I prevent him from seeing other database names.
P.S. I use the GUI
Thank you
View 2 Replies
View Related
May 8, 2012
Basically, I'm working on a stored procedure which will retrieve data based on study parameter passed. The datasource is 'Views'. The name of the view is same for every study except that there is corresponding study name included. For example the views names are something like this for study abc 'v_abc_form' and for study def 'v_def_form'.
Below is the select statement I'm trying to use by declaring @study variable but not able to succeed. I'm not sure how to make the table name dynamic.
Select C1, C2, C3
From v_@study_form
View 7 Replies
View Related
Jan 10, 2006
I have a webpage that should display the database names from the master.sysdatabases in a dropdownlist.
The code for the stored procedure works fine in Query Analyzer and returns the list of all database names under that instance of SQL-Server 2000.
The code:
select name from master.dbo.sysdatabases order by 1doesn't return the sysdatabase names to the ASP.NET webpage when executing the stored procedure. It returns: System.Data.DataRowViewWhy does it do this and how can I fix it?Tx
View 5 Replies
View Related
Sep 7, 2005
I need help to create a stored procedure to get the table names from a database and create a Union view. There are over 100 tables.
View 3 Replies
View Related
Mar 7, 2007
Dear All,
i am making small web application using asp.net, C# ,sql2000.
i want a about regarding how to access view or table from other server to local server. i have base database where there is a view which need to access in my database of local server.that is how to make select query to access view in other server Please help
thanks
View 1 Replies
View Related
Sep 27, 2006
I am allowing users to assign values in a table to field names in a table
for
EAV table transpostion
Does anyone know where I can find a list of ascii valeus or characters that sql server does not like in fieldnames.
IE (ticks, #, :, -,)
I need to allow the user to define fieldnames for values which then I will alter existing tables with that FieldName that they assign.
I know this is a nightmare but it is the contraint I am working under.
View 14 Replies
View Related
Jun 23, 2014
I have the following table records structure:
Code:
drop table ##t1
create table ##t1(col1 int, col2 int, col3 int, col4 int)
insert into ##t1(col1 , col2, col3, col4)
values (1,2,3,4),
(5,6,7,8)
[Code] .....
I can't figure out how to put all nulls at the bottom and have all of my records on the top.
View 11 Replies
View Related
May 19, 2014
I've the table like
create table expense
(
bill_date datetime,
travel int,
fixed int,
food int,
lodge int
)
insert into expense values('01-04-2014',1200,250,0,0)
('02-04-2014','0',0,500,600)
('0-04-2014','800',300,0,0)
Like I've 30th onwards.....
Expecting o/p:
month Travel Fixed Food Lodge
apr-14 200 550 500 600
These cum column values how to make a code ?????
View 5 Replies
View Related
Mar 16, 2008
Hello,I have inserted a drop down list in my Edit template of Grid view. This DDL should control the User_ID who is responsible for the Computer being edited. However I want it to display the User Names rather than the user IDs. I have a COMPUTERS and a USERS table. They are related by having User_ID in both. Below is my grid view with the parts i feel relative highlighted in bold:<asp:GridView ID="GridView1" runat="server" AllowSorting="True" AutoGenerateColumns="False" DataKeyNames="Computer_ID" DataSourceID="SqlDataSource1" CellPadding="4" ForeColor="#333333" GridLines="None"> <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" /> <RowStyle BackColor="#F7F6F3" ForeColor="#333333" /> <Columns> <asp:CommandField ShowEditButton="True"/> <asp:BoundField DataField="Computer_ID" HeaderText="Computer_ID" InsertVisible="False" SortExpression="Computer_ID" ReadOnly="true" /> <asp:BoundField DataField="Computer_Name" HeaderText="Computer Name" SortExpression="Computer_Name"/> <asp:BoundField DataField="Manufacturer" HeaderText="Manufacturer" SortExpression="Manufacturer"/> <asp:TemplateField HeaderText="User Name" SortExpression="Name"> <EditItemTemplate> <asp:DropDownList ID="DDL_Name" runat="server" DataSourceID="SQLDataSource2" DataValueField="User_ID" SelectedValue='<%# Bind("User_ID") %>'></asp:DropDownList> </EditItemTemplate> <ItemTemplate> <asp:Label ID="lblName1" runat="server" Text='<%# Bind("Name") %>'></asp:Label> </ItemTemplate> </asp:TemplateField> </Columns> <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" /> <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" /> <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" /> <EditRowStyle BackColor="#999999" /> <AlternatingRowStyle BackColor="White" ForeColor="#284775" /> </asp:GridView> <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="Data Source=.SQLEXPRESS;AttachDbFilename=C:AjaxControlToolkitWebSite1App_DataAssetDatabase.mdf;Integrated Security=True;User Instance=True" ProviderName="<%$ ConnectionStrings:AssetDatabaseConnectionString.ProviderName%>" SelectCommand="SELECT * FROM USERS" > </asp:SqlDataSource> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="Data Source=.SQLEXPRESS;AttachDbFilename=C:AjaxControlToolkitWebSite1App_DataAssetDatabase.mdf;Integrated Security=True;User Instance=True" ProviderName="<%$ ConnectionStrings:AssetDatabaseConnectionString.ProviderName%>" SelectCommand="SELECT COMPUTERS.Computer_ID, USERS.User_ID, COMPUTERS.Computer_Name, COMPUTERS.Manufacturer, USERS.Name FROM COMPUTERS INNER JOIN USERS ON COMPUTERS.User_ID = USERS.User_ID" UpdateCommandType="StoredProcedure" UpdateCommand="StoredProcedure1" > <UpdateParameters> <asp:Parameter Name="Computer_ID" Type="Int32" /> <asp:Parameter Name="User_ID" Type="Int32"/> <asp:Parameter Name="Name" Type="String" /> <asp:Parameter Name="Computer_Name" Type="String" /> <asp:Parameter Name="Manufacturer" Type="String" /> </UpdateParameters> </asp:SqlDataSource> <br /> <br /> </form></body></html> THANKS =]
View 6 Replies
View Related
Jul 20, 2005
Hi(SQL Server 2000)I have an existing table (t) with a column that is NOT an identity column(t.ID), but it has manually inserted "row numbers". I want to make thiscolumn become an identity column. This column is a key field to othertables, so I want to keep the row numbers that are allready inserted.From the Query Analyzer, how do I do this?Thanks in advance!Regards,Gunnar VøyenliEDB-konsulent asNORWAY
View 3 Replies
View Related
