I'm trying to return a single row of a service order number Table A along with multiple comments from Table B

Here is what I have.

 SELECT sh.[No_], scl.[Date], scl.[Comment]
 from   [Service Header] sh left join [Service Comment Line] scl
    on sh.[No_] = scl.[No_]
 where sh.[No_] = 'SVO-16657'

It returns this:

    NO.             DATE                 COMMENT

 SVO-16657  2015-10-02 00:00:00.000     PART IS READY FOR PICK UP.
 SVO-16657  2015-10-15 00:00:00.000     CONVERSION KITS SHIPPED 10/19/15.
 SVO-16657  2015-10-21 00:00:00.000     ALL OTHER MATERIAL SHIPPED 10/23/15.
 SVO-16657  2015-10-02 00:00:00.000     READY FOR PICK UP.
 SVO-16657  2015-10-15 00:00:00.000     CONVERSION KITS SHIPPING 10/16/15.
 SVO-16657  2015-10-21 00:00:00.000     ALL OTHER MATERIAL SHIPPED.

I want it to read

 SVO-16657  10/2/2015 PART IS READY FOR PICK UP | 10/15/2015 CONVERSION KITS    SHIPPED 10/19/15 | 10/21/2015 ALL OTHER MATERIAL SHIPPED 10/23/15...AND SO ON

self join-SQL -Query

In an interview the below questions asked and couldn't provide an appropriate answer.

Question 1: if i have a table with employee id and manager i want to display it in the below format with employee name and manager.( the employee id 1 have manager null. You need to use self join only)

Question 2: if i have same result got for inner join ,right outer join and left outer join what type of content that table have?

Can anyone help me to find out answer for this?

Yet another "Invalid attempt to read when no data is present." error

I'm trying to pull one field from a view in my database. I determined there is data for this one instance, I correctly set up the SQLDataReader (I believe), the debugger verifies that I have rows in the DataReader, yet when I try to read I get an error.

Here's the code:

    public string[] getReasons(string Accession) {
        string[] reasonList = new string[0];
        SqlParameter accNumber = new SqlParameter();
        accNumber.SqlDbType = System.Data.SqlDbType.VarChar;
        accNumber.ParameterName = "@Accession";
        accNumber.Value = Accession;
        string selectText = "select reason from pendingList where accession = @Accession";
        SqlCommand selectStmt = new SqlCommand(selectText,toPending);
        selectStmt.Parameters.Add(accNumber);

        if (selectStmt.Connection.State == System.Data.ConnectionState.Closed) {
            selectStmt.Connection.Open();
        }
        SqlDataReader pendList = selectStmt.ExecuteReader();

        while (pendList.Read()) {
            reasonList[reasonList.Length] = pendList["reason"].toString();
        }

        pendList.Close();

        return reasonList;
    }

I call getReasons('RAM4658980'). I've verified that the following SQL query

select reason 
from pendingList 
where accession = 'RAM4658980'

returns exactly one row. The pendList variable looks like this:

I'm not sure why I get "Enumeration yielded no results"; and at the reasonList[reasonList.Length] = pendList["reason"].toString(); step, I naturally get the "Invalid attempt to read ..." error. What am I missing?

SQL Server wildcard select with a twist

I am extracting some wildcards from a string type column using certain keywords but for certain keywords in my list i am getting some false positives which I do not want in my output. Some of the keywords in my wildcard select is 'old', 'older' and 'age'

select * from DESCRIPTIONS..LONG
where (DESCR like '% old %'
or DESCR like '% older %'
or DESCR like '% age %'
or DESCR like '%old%'
or DESCR like '%older%'
or DESCR like '%age%')

I want to extract only rows that contain these absolute words but I end up returning strings that include 'management', 'image', 'cold', 'colder' etc. I could remove these true negatives by not looking for the below

DESCR like '%old%'
or DESCR like '%older%'
or DESCR like '%age%'

but in that process I am excluding rows that have special characters like period, comma, slash etc. which are true positives E.g. i would miss strings ending in 'age.' or 'old.' or 'older,' or 'age' when it is the last word in the string without a trailing space.

How do I exclude true negatives and false positives and only get all true positives?

here is a complete list of my keywords separated by a comma.

keywords: newborn, newborns, infant, infants, year, years, child, children, adult, adults, pediatric, old, older, young, younger, age

Thanks

Search all positions of char in string and return as comma separated string

I have string (VARCHAR(255)) that contains only zeros or ones.
I need to search all positions and return them as comma separated string. I've build two queries using solutions from http://ift.tt/1N84Dxq

Here is my code so far:

DECLARE @TERM VARCHAR(5);
SET @TERM = '1';
DECLARE @STRING VARCHAR(255);
SET @STRING = '101011011000000000000000000000000000000000000000';

DECLARE @RESULT VARCHAR(100);
SET @RESULT = '';

SELECT
   @RESULT = @RESULT + CAST(X.pos AS VARCHAR(10)) + ','
FROM
   ( SELECT
      pos = Number - LEN(@TERM)
     FROM
      ( SELECT
         Number
        ,Item = LTRIM(RTRIM(SUBSTRING(@STRING, Number, CHARINDEX(@TERM, @STRING + @TERM, Number) - Number)))
        FROM
         ( SELECT ROW_NUMBER () OVER (ORDER BY [object_id]) FROM sys.all_objects
         ) AS n ( Number )
        WHERE
         Number > 1
         AND Number <= CONVERT(INT, LEN(@STRING))
         AND SUBSTRING(@TERM + @STRING, Number, LEN(@TERM)) = @TERM
      ) AS y
   ) X;

SELECT
   SUBSTRING(@RESULT, 0, LEN(@RESULT));



DECLARE @POS INT;
DECLARE @OLD_POS INT;
DECLARE @POSITIONS VARCHAR(100);
SELECT
   @POSITIONS = '';
SELECT
   @OLD_POS = 0;
SELECT
   @POS = PATINDEX('%1%', @STRING); 
WHILE @POS > 0
   AND @OLD_POS <> @POS
   BEGIN
      SELECT
         @POSITIONS = @POSITIONS + CAST(@POS AS VARCHAR(2)) + ',';
      SELECT
         @OLD_POS = @POS;
      SELECT
         @POS = PATINDEX('%1%', SUBSTRING(@STRING, @POS + 1, LEN(@STRING))) + @POS;
   END;
SELECT
   LEFT(@POSITIONS, LEN(@POSITIONS) - 1);

I'm wondering if this can be done faster/better? I'm searching only for single character positions and I have only two characters that can occur in my string (0 and 1).

I've build two functions using this code, and run them for 1000 records and got same results in same time, so I can't tell which one is better.

for single record second part gives CPU and reads equals to 0 in Profiler, where first piece of code give me CPU=16 and reads=17.

I need to get result that looks like this: 1,3,5,6,8,9 (when multiple occurrences), 3 for single occurence, NONE if there are no ones.

How to Remove the duplicates in SQL Table and concat other part

Let's Assume I have DataTable /SQL Table that represents employee information for example

which contains firstname,lastname,age,company,yearsofexperience,Degree

I want to combine information based on firstname,lastname,age

company,yearsofexperience,Degree must be concat into corresponding cell

firstname   lastname   age   company   yearsofexperience      Degree

john         muller     21    IBM           4years            MBA   
jan          tonny      22,   MSoft         1years            MS
martin       tata       21    apple         2years            PHD
john         Muller     21    sony          3years            MBA
james        muller     21    IBM           4years            PHD   
jan          tonny      22    Telsa         1years            BS     
martin       tata       21    sun           2years            MBA
james        Muller     21    TCS           3years            BS

Please find me way to remove the duplicate rows and make other data concat in particular column

For example in from above example I want combine the information present in the similar other 3 entries

firstname   lastname   age   company            yearsofexperience      Degree

john      muller        21   IBM,sony,              4years,3years,        MBA,MBA   
jan       tonny         22,  MSoft,Telsa            1years,1years         MS,BS
martin    tata          21   apple,sun              2years,2years         PHD,MBA
james     muller        21   IBM,TCS               4years,3years          PHD,BS

Right I am looking for what is best ways to implement this

Its good approach if I Split the Tables in to 2 different tables? may be based on Primary key match. we can concat other entries ?

Please help me out thanks(+1) in advance

Insert new row with current Identity as value

Don't ask me why, but I want to have a column where Identity is stored as varchar. Is it possible to assign this during creation or do I need to Scope Identity and Update?

Normally I would do this:

INSERT INTO [User]
(
    -- id -- this column value is auto-generated,
    useridcr,
    dtcr,
    varcharid
)
VALUES
(
    25,
    '2015-01-30 00:00:00.000'
    ''
)

SET @id = SCOPE_IDENTITY()

UPDATE [User]
SET
    varcharid = @id
WHERE id = @id

But is it possible to know the value of new identity before it's created? So I could use the value in the insert statement.

Thank you