I have a one to many relationship between Flag and StudentFlag. I'm wanting to select each Flag.FlagID that exist as a column that contains the StudentFlag.Value per StudentFlag.StudentID (or null for that column if that StudentFlag row doesn't exist).
This single column appearing multple times in a single dataset has seemed difficult in a select statement. Is it posible in T-SQL?
Tables, Keys, and Relationships
CREATE TABLE [dbo].[Flag] (
[FlagID] [int] NOT NULL ,
[FlagName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[StudentFlag] (
[StudentFlagID] [int] NOT NULL ,
[FlagID] [int] NULL ,
[StudentID] [int] NOT NULL ,
[FlagValue] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Flag] WITH NOCHECK ADD
CONSTRAINT [PK_Flag] PRIMARY KEY CLUSTERED
(
[FlagID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[StudentFlag] WITH NOCHECK ADD
CONSTRAINT [PK_StudentFlag] PRIMARY KEY CLUSTERED
(
[StudentFlagID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[StudentFlag] ADD
CONSTRAINT [FK_StudentFlag_Flag] FOREIGN KEY
(
[FlagID]
) REFERENCES [dbo].[Flag] (
[FlagID]
)
GO
Taking the Northwind database as an example,I have an order table: 'Orders'a order details table: 'Order Details'and a products table: 'Products'For reasons best ignored, I want to produce a query which has columns:Orders.OrderID, Productswhich has results like:10248, 'Queso Cabrales, Singaporean Hokkien Fried Mee, Mozzarella diGiovanni'10249, 'Tofu, Manjimup Dried Apples'________so for those who don't really know what I'm on about and haven't gotaccess to northwind, I want the second cell to contain the returnedcolumn of a second query (but in text format)could anyone suggest a way this could be done? am I entering the landof cursors?Many thanks,Neil
I have created a single FULLTEXT on col2 & col3. suppose i want to search col2='engine' and col3='toyota' i write query as
SELECT
TBL.col2,TBL.col3 FROM
TBL INNER JOIN
CONTAINSTABLE(TBL,col2,'engine') TBL1 ON
TBL.col1=TBL1.[key] INNER JOIN
CONTAINSTABLE(TBL,col3,'toyota') TBL2 ON
TBL.col1=TBL2.[key]
Every thing works well if database is small. But now i have 20 million records in my database. Taking an exmaple there are 5million record with col2='engine' and only 1 record with col3='toyota', it take substantial time to find 1 record.
I was thinking this i can address this issue if i merge both columns in a Single column, but i cannot figure out what format i save it in single column that i can use query to extract correct information. for e.g.; i was thinking to concatinate both fields like col4= ABengineBA + ABBToyotaBBA and in search i use SELECT
TBL.col4 FROM
TBL INNER JOIN
CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABBToyotaBBA"') TBL1 ON
TBL.col1=TBL1.[key] Result = 1 row
But it don't work in following scenario col4= ABengineBA + ABBCorola ToyotaBBA
SELECT
TBL.col4 FROM
TBL INNER JOIN
CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABB*ToyotaBBA"') TBL1 ON
TBL.col1=TBL1.[key]
Result=0 Row Any idea how i can write second query to get result?
Hi. I want to return multiple rows into a single row in different columns. For example my query returns something like thisThe query looks like thisSelect ID, TYPE, VALUE From myTable Where filtercondition = 1ID TYPE VALUE1 type1 121 type2 152 type1 16 2 type2 19Each ID will have the same number of types and each type for each ID might have a different value. So if there are only two types then each ID will have two types. Now I want to write the query in such a way that it returnsID TYPE1 TYPE2 VALUE1 VALUE21 type1 type2 12 152 type1 type2 16 19Type1, Type2, Value1, and Value2 are all dynamic. Can someone help me please. Thank you.
SELECT 'Type'[Type] ,CASE WHEN code='09' THEN SUM(Amt/100) ELSE 0 END ,CASE WHEN code='10' THEN SUM(Amt/100) ELSE 0 END ,CASE WHEN code='11' THEN SUM(Amt/100) ELSE 0 END ,CASE WHEN code='12' THEN SUM(Amt/100) ELSE 0 END FROM Table1 WHERE (Code BETWEEN '09' AND '12') GROUP BY Code
and the output
Column 1 Column 2 Column 3 Column 4 Type 14022731.60 0.00 0.00 0.00 Type 0.00 4749072.19 0.00 0.00 Type 0.00 0.00 149214.04 0.00 Type 0.00 0.00 0.00 792210.10
How can I modify the query to come up with output below,
Suppose that I have a table with following values Table1 Col1 Col2 Col3 ----------------------------------------------------------- P3456 C935876 T675 P5555 C678909 T8888
And the outcome that I want is: CombinedValues(ColumnName) ---------------------------------------------- P3456 - C935876 - T675 P5555 - C678909 - T8888
where CombinedValues column contains values of coulmn 1,2 & 3 seperated by '-' So is there any way to achieve this?
I have multiple databases in the server and all my databases have tables: stdVersions, stdChangeLog. The stdVersions table have field called DatabaseVersion which stored the version of the database. The stdChangeLog table have a field called ChangedOn which stored the date of any change made in the database.
I need to write a query/stored procedure/function that will return all the database names, version and the date changed on. The results should look something like this:
I have a description field in a table which also stores unit of measure in the same column but with some space between them, I need to split these into two different columns.
I have a data flow task in which there is a OLEDB source, derived column item, and a oledb destination. My source is a SQL command, that returns some values. I have some values, that I define in the derived columns, and set default values under the expression column. My question is, I also have some destination columns which in my OLEDB destination need another SQL command. How would I do that? Can I attach two or more OLEDB sources to one destination? How would I accomplish that? Thanks
I'm working on a script to merge multiple columns(30) into a single column separated by a semicolons, but I'm getting the following error below. I tried to convert to the correct value. but I'm still getting an error.
Error: "Conversion failed when converting the varchar value ';' to data type tinyint".
I have a program which feeds back information to a table within my database. The annoying thing is that only 1 of the table cells has all of the information I need so essentially I have a large string of data I need to retrieve values from.I retrieve my cell with a simple select details from alerts where ID = (alert number).The results I have are as follows:
As you can see I have a list (its larger but this is a sample), the only thing I need are the values for each section so for last processed i want the 2911 so I can use it on my dashboard and the last received I want the 10 for that section.i have read about using substrings but am really not sure where to start and how I'd go about getting these values to then use elsewhere?
HiI want to achieve the following transformation of data using a storedprocedure.Sourcecol1 col2(varchar)-------------------------1 1.11 1.22 2.12 2.22 2.3=================Desired Resultcol1 col2(varchar)--------------------------1 1.1 | 1.22 2.1 | 2.2 | 2.3=====================Thanks in advance. :)- Parth
I'm retreiving data from a SAP BW data source. In the dataset designer I can build a query that returns a single value, the data returns successfully in the designer. When I switch to the layout mode I can place the field of the datset in table / matrix / textbox. However when i switch to preview mode or deploy the report and try to see them in Report Manager the report fails to render with the following message:
Query Execution Failed for dataset 'XXX'
These columns don't currently have unique values.
Its a single cell datset?!?!
I can successfully retreive data from other SAP Infocubes and queries. I also cannot replicate the error using a SQL Server datasource. The stack trace error looks like this:
w3wp!processing!6!5/9/2007-16:57:41:: e ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for data set 'DataSet1'., ; Info: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for data set 'DataSet1'. ---> System.ArgumentException: These columns don't currently have unique values. at System.Data.ConstraintCollection.AddUniqueConstraint(UniqueConstraint constraint) at System.Data.ConstraintCollection.Add(Constraint constraint, Boolean addUniqueWhenAddingForeign) at System.Data.DataTable.set_PrimaryKey(DataColumn[] value) at Microsoft.ReportingServices.XmlaClient.CubeMetadataCache.CreatePrimaryKeys(DataTable table, InternalObjectType objectType) at Microsoft.ReportingServices.XmlaClient.CubeMetadataCache.Populate(InternalObjectType objectType) at Microsoft.ReportingServices.XmlaClient.CubeMetadataCache.Microsoft.ReportingServices.XmlaClient.IMetadataCache.Populate(InternalObjectType objectType) at Microsoft.ReportingServices.XmlaClient.ObjectMetadataCache.Microsoft.ReportingServices.XmlaClient.IObjectCache.Populate() at Microsoft.ReportingServices.XmlaClient.CacheBasedFilteredCollection.PopulateCollection() at Microsoft.ReportingServices.XmlaClient.DimensionPropertyCollectionInternal.Find(String index) at Microsoft.ReportingServices.XmlaClient.DimensionPropertyCollection.Find(String index) at Microsoft.ReportingServices.DataExtensions.SapBw.DataReader.FindOptionalPropertyName(String internalColumnName, CubeDef cube, Boolean fIsYukonSP1) at Microsoft.ReportingServices.DataExtensions.SapBw.DataReader.GenerateColumnInformation(CubeDef cube, Boolean fIsYukonSP1) at Microsoft.ReportingServices.DataExtensions.SapBw.DataReader..ctor(AdomdDataReader dataReader, CubeDef cube) at Microsoft.ReportingServices.DataExtensions.SapBw.SapBwCommand.ExecuteReader(CommandBehavior behavior) at Microsoft.ReportingServices.ReportProcessing.ReportProcessing.RuntimeDataSetNode.RunDataSetQuery()
Any ideas why this happening and how I can get around it?
Thanks
Sanjay
ps Why would you want to put a unique constraint on a read only single table dataset?
i have labels for data stored in one cell eg: item1; item22; item231; and i want to convert it in following output (probably using substring and charindex)
I am converting a site from asp to .net and the previus writer did something I never saw.(I am somewhat new to programming all together). He put multiple values in a single cell of the database. I have always learned to use a seperate column for each value. Is this a classic asp thing and there are better ways now? Or is this something I should do myself? The columns type is text and the contents look like this:<Details Expertise="bla bla" Description="We are a three-divisional company ...," WebSite="www.blabla.com" AccountLevel="Free" AccountStatus="Active" WorkHomeZIP="22222" ContractStartDate="01/01/2003" ContractEndDate="12/31/2003"><Address Location="Work" State="Oh" ZIP="22222"/><Email Location="Work" Value=""/><Phone Location="Fax" Code="9801" Ext=""/></Details>
Is it possible to stack two images on top of each other in a table ?
Let me clarify where i'm going to. I have a report where in one table i would like to display (in a graphical way) how much a sales person has sold towards the whole sales amount. To do so i would like to set my background of my cell to yellow and then use an image (just a green rectangle) where i adjust the padding according to the percentage. When i set my background to yellow and i insert my image in the cell, the textbox cell properties disapear and are replaced with the image properties. So also my background that was defined in my textbox is gone. Now i was thinking if i could set two images on top of each other (a yellow rectangle and a green rectangle). But when i do that, one image is replaced with another.
Am i doing something wrong or is there another way to achieve what i want to do?
My team is using TFS for work item management and I'd like to provide a week schedule matrix that displays: - weekdays on the x-axis - persons on the y-axis - and the assigned workitems in the cell.
This works fine if there's only one WIT (work item) per day but some WIT's are shorter and thus the resource is assigned more than one a day. My dataset may return something like this:
Date Person WIT 28/3 Mark Task1 28/3 Mark Task2 28/3 Hank Task3 29/3 Mark Task2 29/3 Hank Task3
In this case I'd like the matrix to look something like this, i.e. the two tasks Task1 and Task2 in the same cell: Mark Hank 28/3 Task1 Task3 Task2 29/3 Task2 Task3
I've set up the matrix in the report but I only get the first item per day to show. Mark Hank 28/3 Task1 Task3 29/3 Task2 Task3
The default definition in the cell was "=First(Field!TaskName.Value)", so I figured I'd solved the issue by changing it to "=Field!TaskName.Value", but it didn't.
I am trying to create an SSRS report to display a single row of Household information (from CRM Dynamics) Household being the Parent Entity, with 1:N relationship with Contact Entity.
How would I go about showing all the Firstnames (from Contact) in the same cell like the below?
Hi, I'm working with MRS and I've got a table with a lot of entries. For each value in the table I'm trying to get the text colour to be set to 'red' when the value of the cell is less than 0. Otherwise remain black.
I can do this by setting the colour property cell by cell. But I have a lot of cells in the table. Is there a way to set the statement to apply to ALL cells in the table?
Basically I'm asking if there is a way to set the property in bulk instead of going through tediously cell by cell.