For example:
Student ID: 100
Student Name: Kelly Preston
Subjects: Geography, History, Math
How can I store these values in a database table?
I know the normal "INSERT" statement, but how would I store the multiple subjects for a single student ID?
My "Student ID" is auto generated. If I create a new row for each subject, the Student ID will be different for each subject, which I dont want.
Or I can create a new field called "RowNumber" and keep that the primary key..
For example:
Row Number StudentID StudentName Subject
1 100 Kelly Geography
2 100 Kelly History
3 100 Kelly Math
If this is the only way to store the multiple sibjects, then for a given student ID (say 100), how can I retreieve the associated name and subjects? What is the query for that?
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?
I am trying to filter my columns on multiple values. I need them all to be OR because I want it to look through all of the columns and wherever the value matches to not include in the view. My WHERE clause that I thought would work looked like this..
WHERE (NOT (RTPL_VOLUME_DATA_1.SYMBOL LIKE '%spot%')) AND (NOT (RTPL_VOLUME_DATA_1.ISSPREAD = 'True')) AND (NOT (RTPL_VOLUME_DATA_1.GMIPRODUCTCODE = 'Internal')) AND (RTPL_VOLUME_DATA_1.TAG_COMMENT IN ('[]', '[Gscalp]', '[TT]', '[GX2]', '[NA]', '[STELLAR]')) OR (RTPL_VOLUME_DATA_1.TAG_COMMENT IS NULL)
However this does not work and provide the data needed. I then thought that if I replaced all the AND's with OR's that would work, but here it does not filter anything.. not sure where to go from here.
I am writing a stored procedure that needs a access individual entries in a column with multiple entries delimited by a comma(yeah i know, not 1st NF) . Like this:
Key
NotANormalizedCol
1
1324, 5124, 5435,5467
2
423, 23, 5345
3
52334, 53443, 1224
4
12, 4, 1243,66
is there a function that returns a substring given a delimiter character? the only substring returning function that i found are the LEFT and RIGHT that returns fixed length substring.
I am pretty new to this, so I apologize if this is a trivial questions
Select ID, FIRST(forename), FIRST(surname) from table1 GROUP BY ID;
I know First doesn't work in TSQL, I used to use it in Access and now need to run something like that in TSQL. Simply getting unique ID with first forename and surname, cause there are some dupes in a table.
There are records like:
ID forename surname -------------------------------- 1 John Kormack 1 James Dope 2 Erin Dupes 3 Will Hugh 3 Walter Heisenberg
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?
If I have a column in one table (contracts) that contains a set of values (codes that identify the fields contract code) like as follows...
Code:
Contracts table -------------------------------- Concode - description -------------------------------- KIDD - Kidderminster General UNIV - University Hospitals
and then another table (controls) which has a column called contracts where the data within the 'Contracts' field is set out like the following (yes that is right, each of the data in this column are the Foreign Keys which are separated by '/' in which I need to query against the contracts table ) ...
With the those tables and the columns like they are, how can I get my query to display the following information...
PHP Code:
--------------------------------------------------- Con(Controls table) - Description(contracts table) --------------------------------------------------- BA - Kidderminster General BA - University Hospitals ---------------------------------------------------
I'm guessing my first job is to somehow extract those Foreign Keys from the 'Contracts' column in the 'Control' table
In a table I have some rows with flag A & B for a scode, some scode with only A and some are only B flags.
I would like to fetch all rows with flag A when both flags are present, no rows with B should be fetched. Fetch all rows when only single flags are present for a scode.How to achieve this using TSQL code.
SELECT ID1, ID2, type, (case when type = '1' then sum(value) else '0' end) as Value1, (case when type = '3' then sum(value) else '0' end) as Value2, (case when type <> '1' and type <> '3' then sum(value) else '0' end) as Value3 FROM table1 WHERE ID1 = 'x' and ID2= 'y' GROUP BY ID1, ID2, Type
I was to split each record into multiple columns. The problem is some records need to be split into only 1 column, others may need to be split into more. Also need to remove the "/"'s. This is all dependent on where a "/" is found. Been beating my head for a while and getting nowhere.
So:
create table #foo (myPK int, c1 nvarchar(425)) insert into #foo values (1,'/folder1') insert into #foo values (2,'/lvl1/folder2') insert into #foo values (3,'/folder1/lvl2/folder3') insert into #foo values (4,'/f1/folder2/lvl3/fldr4')
Is it possible to assign multiple columns from a SQL query to one variable. In the below query I have different variable (email, fname, month_last_taken) from same query being assigned to different columns, can i pass all columns to one variable only and then extract that column out of that variable later? This way I just need to write the query once in the complete block.
DECLARE @email varchar(500) ,@intFlag INT ,@INTFLAGMAX int ,@TABLE_NAME VARCHAR(100)
I have a table #vert where I have value column. This data needs to be updated into two channel columns in #hori table based on channel number in #vert table.
CREATE TABLE #Vert (FILTER VARCHAR(3), CHANNEL TINYINT, VALUE TINYINT) INSERT #Vert Values('ABC', 1, 22),('ABC', 2, 32),('BBC', 1, 12),('BBC', 2, 23),('CAB', 1, 33),('CAB', 2, 44) -- COMBINATION OF FILTER AND CHANNEL IS UNIQUE CREATE TABLE #Hori (FILTER VARCHAR(3), CHANNEL1 TINYINT, CHANNEL2 TINYINT) INSERT #Hori Values ('ABC', NULL, NULL),('BBC', NULL, NULL),('CAB', NULL, NULL) -- FILTER IS UNIQUE IN #HORI TABLE
One way to achieve this is to write two update statements. After update, the output you see is my desired output
UPDATE H SET CHANNEL1= VALUE FROM #Hori H JOIN #Vert V ON V.FILTER=H.FILTER WHERE V.CHANNEL=1 -- updates only channel1 UPDATE H SET CHANNEL2= VALUE FROM #Hori H JOIN #Vert V ON V.FILTER=H.FILTER WHERE V.CHANNEL=2 -- updates only channel2 SELECT * FROM #Hori -- this is desired output
my channels number grows in #vert table like 1,2,3,4...and so Channel3, Channel4....so on in #hori table. So I cannot keep writing too many update statements. One other way is to pivot #vert table and do single update into #hori table.
I have a situation in SSRS to get the common values between the two columns where the values are sorted comma separated as below.Ex:
ColumnA : abc,cde,efg ColumnB : cde,xyz,abc
the result in
ColumnC : cde,abc
similarly Column A and B will have n number records. I need to right an expression or the Code function to get the required result in ColumnC. I am using SharePoint Lists as Datasource. Cannot write SQL query to achieve this requirement.
Basically, I'm given a daily schedule on two separate rows for shift 1 and shift 2 for the same employee, I'm trying to align both shifts in one row as shown below in 'My desired results' section.
Sample Data:
;WITH SampleData ([ColumnA], [ColumnB], [ColumnC], [ColumnD]) AS ( SELECT 5060,'04/30/2015','05:30', '08:30' UNION ALL SELECT 5060, '04/30/2015','13:30', '15:30' UNION ALL SELECT 5060,'05/02/2015','05:30', '08:30' UNION ALL SELECT 5060, '05/02/2015','13:30', '15:30'
I have a business need to create a report by query data from a MS SQL 2008 database and display the result to the users on a web page. The report initially has 6 columns of data and 2 out of 6 have JSON data so the users request to have those 2 JSON columns parse into 15 additional columns (first JSON column has 8 key/value pairs and the second JSON column has 7 key/value pairs). Here what I have done so far:
I found a table value function (fnSplitJson2) from this link [URL]. Using this function I can parse a column of JSON data into a table. So when I use the function above against the first column (with JSON data) in my query (with CROSS APPLY) I got the right data back the but I got 8 additional rows of each of the row in my table. The reason for this side effect is because the function returned a table of 8 row (8 key/value pairs) for each json string data that it parsed.
1. First question: How do I modify my current query (see below) so that for each row in my table i got back one row with 19 columns.
SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.* FROM PRODUCT A CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B
If updated my query (see below) and call the function twice within the CROSS APPLY clause I got this error: "The multi-part identifier "A.ITEM6" could be be bound.
2. My second question: How to i get around this error?
SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.*, C.* FROM PRODUCT A CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B, fnSplitJson2(A.ITEM6,NULL) C
I am using Microsoft SQL Server 2008 R2 version. Windows 7 desktop.
I'd like to first figure out the count of how many rows are not the Current Edition have the following:
Second I'd like to be able to select the primary key of all the rows involved
Third I'd like to select all the primary keys of just the rows not in the current edition
Not really sure how to describe this without making a dataset
CREATE TABLE [Project].[TestTable1]( [TestTable1_pk] [int] IDENTITY(1,1) NOT NULL, [Source_ID] [int] NOT NULL, [Edition_fk] [int] NOT NULL, [Key1_fk] [int] NOT NULL, [Key2_fk] [int] NOT NULL,
[Code] .....
Group by fails me because I only want the groups where the Edition_fk don't match...
it is my first post on this forum, please be patient if i miss any important bit of information.
i am transporting data from a legacy system into mssql 2k5 using SSIS. among those column of a dataset there are 13 columns, all necessary for operational reasons, that i need to ensure data consistance.
i believe i could do this check using the lookup data flow item, but surely there must be a way to do it in a more streamlined fashion.
since column names contain numbers to distinguish the version, eg; col01, col02, col03 .. col13.
i thought i could include the lookup within a loop and use a couple of variables to do this trick, but since i have not done it before i am asking for some sort of guidance from a guru among you folks.
please let me know if further clarification is necessary.
I am SSRS user, We have a .net UI from where we want to pass multi select values, but these values are comma separated in the database. how can I write a sql query such that when I select multi values on my UI, the comma separated values are take care of.
I am facing a problem in writing the stored procedure for multiple search criteria.
I am trying to write the query in the Procedure as follows
Select * from Car where Price=@Price1 or Price=@price2 or Price=@price=3 and where Manufacture=@Manufacture1 or Manufacture=@Manufacture2 or Manufacture=@Manufacture3 and where Model=@Model1 or Model=@Model2 or Model=@Model3 and where City=@City1 or City=@City2 or City=@City3
I am Not sure of the query but am trying to get the list of cars that are to be filtered based on the user input.
I concatenate multiple rows from one table in multiple columns like this:
--Create Table CREATE TABLE [Person].[Person_1]( [BusinessEntityID] [int] NOT NULL, [PersonType] [nchar](2) NOT NULL, [FirstName] [varchar](100) NOT NULL, CONSTRAINT [PK_Person_BusinessEntityID_1] PRIMARY KEY CLUSTERED
[Code] ....
This works very well, but I want to concatenate more rows with different [PersonType]-Values in different columns and I don't like the overhead, of using the same table in every subquery ([Person_1]). Is there a more elegant way to do this, without using a temp table or something else?
I am rather new to reporting on SQL Server 2005 so please be patient with me.
I need to create a report that will generate system information for a server, the issue im having is that the table I am having to gather the information from seems to only allow me to pull off data from only one row.
For example,. Each row contains a different system part (I.e. RAM) this would be represented by an identifier (1), but I to list each system part as a column in a report
The table (System Info) looks like:-
ID | System part | 1 | RAM 2 | Disk Drive 10| CPU 11| CD ROM |
Which
So basically I need it to look like this.
Name | IP | RAM | Disk Drive| ---------------------------------------------- A | 127.0.0.1 | 512MB | Floppy
So Far my SQL code looks like this for 1 item SELECT SYSTEM PART FROM System Info WHERE System.ID = 1
How would I go about displaying the other system parts as columns with info
I am trying to create a report using Reporting Services.
My problem right now is that the way the table is constructed, I am trying to pull 3 seperate values i.e. One is the number of Hours, One is the type of work, and the 3rd is the Grade, out of one column and place them in 3 seperate columns in the report.
I can currently get one value but how to get the information I need to be able to use in my reports.
So far what I've been working with SQL Reporting Services 2005 I love it and have made several reports, but this one has got me stumped.
Any help would be appreciated.
Thanks.
I might not have made my problem quite clear enough. My table has one column labeled value. The value in that table is linked through an ID field to another table where the ID's are broken down to one ID =Number of Hours, One ID = Grade and One ID= type of work.
What I'm trying to do is when using these ID's and seperate the value related to those ID's into 3 seperate columns in a query for using in Reporting Services to create the report
As you can see, I'm attempting to change the name of the same column 3 times to reflect the correct information and then link them all to the person, where one person might have several entries in the other fields.
As you can see I can change the names individually in queries and pull the information seperately, it's when roll them altogether is where I'm running into my problem
Thanks for the suggestions that were made, I apoligize for not making the problem clearer.
Here is a copy of what I'm attempting to accomplish. I didn't have it with me last night when posting.
--Pulls the Service Opportunity
SELECT cs.value AS "Service Opportunity"
FROM Cstudent cs
INNER JOIN cattribute ca ON ca.attributeid = cs.attributeid
WHERE ca.name = 'Service Opportunity'
--Pulls the Number of Hours
SELECT cs.value AS 'Number of Hours'
FROM Cstudent cs
INNER JOIN cattribute ca ON ca.attributeid =cs.attributeid
WHERE ca.name ='Num of Hours'
--Pulls the Person Grade Level
SELECT cs.value AS 'Grade'
FROM Cstudent cs
INNER JOIN cattribute ca ON ca.attributeid =cs.attributeid
WHERE ca.name ='Grade'
--Pulls the Person Number, First and Last Name and Grade Level
SELECT s.personnumber, s.lastname, s.firstname, cs.value as "Grade"
FROM student s
INNER JOIN cperson cs ON cs.personid = s.personid
INNER JOIN cattribute ca ON ca.attributeid = cs.attributeid
WHERE cs.value =(SELECT cs.value AS 'Grade'
WHERE ca.attributeid = cs.attributeid AND ca.name='Grade')
I'd like to generate the calculated column SCORE based on various scenarios in the other columns. eg.
if n1<10 and n2<10 then i=i + 1 if n4-n3=1 then i=i + 1 if more than 2 consecutive numbers then i=i + 1
So, I need to build the score. I've tried the procedure below and it works as a pass or fail but is too limiting. I'd like something that increments the variable @test1.
declare @test1 int set @test1=0 select top 10 n1,n2,n3,n4,n5,n6, case when ( n1=2 and n2>5 ) then @test1+1 else @test1 end as t2 from allNumbers
I want to search in fulltextindexes for multiple searchterms in multiple columns. The difficulty is: I don't want only the records with columns that contains both searchterms. I also want the records of which one column contains one of the searchterm ans another column contains one of the searchterms.
For example I search for NETWORK and PERFORMANCE in two columns. Jobdescr_________________________|Jobtext Bad NETWORK PERFORMANCE________|Slow NETWORK browsing in Windows XP Bad application PERFORMANCE_______|Because of slow NETWORK browsing, the application runs slow.
I only get the first record because JobDescr contains both searchterms I don't get the second record because none of the columns contains both searchterms
I managed to find a workaround:
SELECT T3.jobid, T3.jobdescr FROM (SELECT jobid FROM dba.job WHERE contains(jobdescr, 'network*') or CONTAINS(jobtext, 'network*') ) T1 INNER JOIN (SELECT jobid FROM dba.job WHERE contains(jobdescr, 'performance*') or CONTAINS(jobtext, 'performance*')) T2 ON T2.Jobid = T1.Jobid INNER JOIN (SELECT jobid, jobdescr FROM dba.job) T3 ON T3.Jobid = T1.Jobid OR T3.Jobid = T2.JobId It works but i guess this will result in a heavy database load when the number of searchterms and columns will increase.
I have an Parent table (Parentid, LastName, FirstName) and Kids table (Parentid, KidName, Age, Grade, Gender, KidTypeID) , each parent will have multiple kids, I need the result as below:
I previously posted a problem with result set bindings but I have not been able to resolve my problem. I guess all this comes with being new to programming in this environment! Anyway, I am trying to figure out how to process from an ADO.NET connection multiple rows with multiple columns. I have to read and manipulate each row. I was originally looking at using a foreach loop but have not been able to get it to work. One reply to my previous thought I should be using a data task to accomplish this. Could someone tell me the best way to handle this situation? As a note, I am new to programming in SSIS and basically trying to learn it as I go so please bear with me! Thanks in advance!
