How to insert multiple values in a single column

I need to display output as shown below. In Col1, Col2 and Col3 I want to insert values from subqueries. But do not want to use sub-reports... is there any alternative to subqueries.

In Col1, Col2 and Col3 there can be any number of values.

Company

BankCol1Col2Col3AstroTechICICI

123

5

34

MindTreeHDFC

54

8

why don't you want to use a sub-report?

|||

It takes very long time, because i have 17 columns in the report.. its not fisible to insert 15 sub-reports

|||

Ah I understand, and agree. Not sure how else to implement this though...could you insert another table into your main table's cells?

|||

Not resolved yet... any ideas...

|||

One idea would be to build your data using a view. Capture the main data (Company, Bank, ID) and then capture each columns' data (based on the ID in first query). Select from the view.

I think your data would be more like:

ID Company Bank ManagerNames BranchOffices TopSalesNames

1 CompanyA BankOne Jerry, Ted, Lisa Omaha, Chicago Fred, Mary

2 CompanyB BankTwo Paul Lincoln, Springfield, Florence William, John

Not sure if this meets your needs. You may be able to format the multi-value rows once in the report. Hope this helps.

|||

thanks... I will try this

How to insert a space after each Manager Starts.

hi,
guys
i have query which given below output given below

manager personlevel person name
20851 Howard Wilson1
20852Howard Wilson2
20853Howard Wilson3
20854Howard Wilson4
20855Howard Wilson5
60861Andrew Saxon
60862Andrew Saxon
60863Ian Thompson
60864Ian Thompson
60865Phil Dargan

what i want is after a manager ends i want a null to be inserted for
each of there columns
so that i can distinguish that when a new manager starts

so thatt output looks like this

manager personlevel person name
20851 Howard Wilson1
20852Howard Wilson2
20853Howard Wilson3
20854Howard Wilson4
20855Howard Wilson5
null null null
60861Andrew Saxon
60862Andrew Saxon
60863Ian Thompson
60864Ian Thompson
60865Phil Dargan

Brlliant minds any solution for this..
i know can i loop through the records and do it
and check for a new manager
but i want a better solution ..
give me your ideads folks..

Regards,
Navin MahindrooHi

You don't post the DDL or the current query so it is hard to know what your
SQL is.

Assuming something like:

SELECT Manager, Personlevel, PersonName from Mgmt

You could try (untested)

SELECT Manager, Personlevel, PersonName from
( SELECT Manager as Id, Manager, Personlevel, PersonName from Mgmt
UNION
SELECT DISTINCT Manager, NULL, NULL, NULL from Mgmt
ORDER BY Id ASC, Manager DESC ) M

John

"Navin" <navinsm2@.rediffmail.com> wrote in message
news:5dc7f532.0306300051.7b6d1f67@.posting.google.c om...
> hi,
> guys
> i have query which given below output given below
> manager personlevel person name
> 2085 1 Howard Wilson1
> 2085 2 Howard Wilson2
> 2085 3 Howard Wilson3
> 2085 4 Howard Wilson4
> 2085 5 Howard Wilson5
> 6086 1 Andrew Saxon
> 6086 2 Andrew Saxon
> 6086 3 Ian Thompson
> 6086 4 Ian Thompson
> 6086 5 Phil Dargan
> what i want is after a manager ends i want a null to be inserted for
> each of there columns
> so that i can distinguish that when a new manager starts
> so thatt output looks like this
> manager personlevel person name
> 2085 1 Howard Wilson1
> 2085 2 Howard Wilson2
> 2085 3 Howard Wilson3
> 2085 4 Howard Wilson4
> 2085 5 Howard Wilson5
> null null null
> 6086 1 Andrew Saxon
> 6086 2 Andrew Saxon
> 6086 3 Ian Thompson
> 6086 4 Ian Thompson
> 6086 5 Phil Dargan
> Brlliant minds any solution for this..
> i know can i loop through the records and do it
> and check for a new manager
> but i want a better solution ..
> give me your ideads folks..
> Regards,
> Navin Mahindroo|||Hi Navin M,

Same other way round.

SELECT 'N' 'GRP_SEP',manager, personlevel ,[person name] FROM
TableName
UNION ALL
SELECT DISTINCT 'Y',manager,NULL,NULL FROM TableName
ORDER BY manager,GRP_SEP ASC

Group seperator is added to explicitly know that row with 'Y' is group
seperator and avoid null conflit if personlevel and name both are
null.

Also note that Manager field has appropriate index on it.

hope this helps you.

Thanks Amit.

navinsm2@.rediffmail.com (Navin) wrote in message news:<5dc7f532.0306300051.7b6d1f67@.posting.google.com>...
> hi,
> guys
> i have query which given below output given below
> manager personlevel person name
> 20851 Howard Wilson1
> 20852Howard Wilson2
> 20853Howard Wilson3
> 20854Howard Wilson4
> 20855Howard Wilson5
> 60861Andrew Saxon
> 60862Andrew Saxon
> 60863Ian Thompson
> 60864Ian Thompson
> 60865Phil Dargan
> what i want is after a manager ends i want a null to be inserted for
> each of there columns
> so that i can distinguish that when a new manager starts
> so thatt output looks like this
> manager personlevel person name
> 20851 Howard Wilson1
> 20852Howard Wilson2
> 20853Howard Wilson3
> 20854Howard Wilson4
> 20855Howard Wilson5
> null null null
> 60861Andrew Saxon
> 60862Andrew Saxon
> 60863Ian Thompson
> 60864Ian Thompson
> 60865Phil Dargan
> Brlliant minds any solution for this..
> i know can i loop through the records and do it
> and check for a new manager
> but i want a better solution ..
> give me your ideads folks..
> Regards,
> Navin Mahindroo|||Hi

Got around to testing it... you can't use the order by in the derived
table!

SELECT Manager, Personlevel, PersonName from
( SELECT Manager as Id, Manager, Personlevel, PersonName from Mgmt
UNION
SELECT DISTINCT Manager, NULL, NULL, NULL from Mgmt
) M
ORDER BY id, Manager Desc

John

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:3f0009d7$0$18490$ed9e5944@.reading.news.pipex. net...
> Hi
> You don't post the DDL or the current query so it is hard to know what
your
> SQL is.
> Assuming something like:
> SELECT Manager, Personlevel, PersonName from Mgmt
> You could try (untested)
> SELECT Manager, Personlevel, PersonName from
> ( SELECT Manager as Id, Manager, Personlevel, PersonName from Mgmt
> UNION
> SELECT DISTINCT Manager, NULL, NULL, NULL from Mgmt
> ORDER BY Id ASC, Manager DESC ) M
> John
> "Navin" <navinsm2@.rediffmail.com> wrote in message
> news:5dc7f532.0306300051.7b6d1f67@.posting.google.c om...
> > hi,
> > guys
> > i have query which given below output given below
> > manager personlevel person name
> > 2085 1 Howard Wilson1
> > 2085 2 Howard Wilson2
> > 2085 3 Howard Wilson3
> > 2085 4 Howard Wilson4
> > 2085 5 Howard Wilson5
> > 6086 1 Andrew Saxon
> > 6086 2 Andrew Saxon
> > 6086 3 Ian Thompson
> > 6086 4 Ian Thompson
> > 6086 5 Phil Dargan
> > what i want is after a manager ends i want a null to be inserted for
> > each of there columns
> > so that i can distinguish that when a new manager starts
> > so thatt output looks like this
> > manager personlevel person name
> > 2085 1 Howard Wilson1
> > 2085 2 Howard Wilson2
> > 2085 3 Howard Wilson3
> > 2085 4 Howard Wilson4
> > 2085 5 Howard Wilson5
> > null null null
> > 6086 1 Andrew Saxon
> > 6086 2 Andrew Saxon
> > 6086 3 Ian Thompson
> > 6086 4 Ian Thompson
> > 6086 5 Phil Dargan
> > Brlliant minds any solution for this..
> > i know can i loop through the records and do it
> > and check for a new manager
> > but i want a better solution ..
> > give me your ideads folks..
> > Regards,
> > Navin Mahindroo

how to improve this transaction

Hello,

I have four different transactions such as below and I do one insert and one update in each transaction and it seem it is slow and creates deadlock with the user interface.

These transactions are performed against the tables that users are accessing with another user interface. I have following two questions:

1. T2.TextField1 and TextField2 = @.TextField2 are Ok, Nok fields so I did not put index since only two distinct values. Should I put indexes on these fields?

2. Can I make this transaction let user interface do its task in case accessing the same rows, I can start transaction again but I do not want users get disturbed?

.

BEGIN TRANSACTION pTrans

BEGIN

INSERT INTO T1

(fields)

SELECT (fields)

FROM T2 INNER JOIN View1 ON T2.TrID = View1.MyTableID

WHERE (T2.TextField1 = @.TrType AND T2.TextField2 = @.TextField2)

UPDATE T2

SET TextField2 = 'Ok', TextField2Date=@.MyRunDateTime

FROM T2

WHERE (TextField1 = @.TrType AND TextField2 = @.TextField2)

IF @.@.ERROR <> 0

BEGIN

rollback transaction pTrans

return(-1)

END

ELSE

BEGIN

commit transaction pTrans

END

END

What's the schemas of the 3 tables T1,T2 and View1? I tried a test script using your statements, the query went fine and no deadlock occurred.

Q1. Do not put index on low selective column (as you said only 2 distinct values), because it dosen't make sense. Here are some guidelines for choosing columns to index from "Programming a Microsoft? SQL Server? 2000 Database":

Indexes are useful, but they consume disk space and incur overhead and maintenance costs. Consider the following facts and guidelines about indexes:

When you modify data on an indexed column, SQL Server updates the associated indexes.
|||

Thanks for your help, can you let me know how I can make this transaction low isolation level? I am quite new in the transaction isolation level.

|||

Simply use the SET TRANSACTION ISOLATION LEVEL command to set the session option, you can refer to this link:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_set-set_74bw.asp

how to improve this transaction

Hello,

I have four different transactions such as below and I do one insert and one update in each transaction and it seem it is slow and creates deadlock with the user interface.

These transactions are performed against the tables that users are accessing with another user interface. I have following two questions:

1. T2.TextField1 and TextField2 = @.TextField2 are Ok, Nok fields so I did not put index since only two distinct values. Should I put indexes on these fields?

2. Can I make this transaction let user interface do its task in case accessing the same rows, I can start transaction again but I do not want users get disturbed?

.

BEGIN TRANSACTION pTrans

BEGIN

INSERT INTO T1

(fields)

SELECT(fields)

FROMT2 INNER JOIN View1 ON T2.TrID = View1.MyTableID

WHERE (T2.TextField1 = @.TrType AND T2.TextField2 = @.TextField2)

UPDATET2

SETTextField2 = 'Ok',TextField2Date=@.MyRunDateTime

FROMT2

WHERE (TextField1 = @.TrType AND TextField2 = @.TextField2)

IF @.@.ERROR <> 0

BEGIN

rollback transaction pTrans

return(-1)

END

ELSE

BEGIN

commit transaction pTrans

END

END

i think youre just maintaing the data on T2

based from the inserted values on T1.

how about just doing the insert on T1

and placing the update codes on

an "after insert trigger" in t1.

a transaction may not be necessary in this case

the previous design will cause a lot of deadlock in the system

|||

Jim,

You should consider defining indexes on fields listed in "where" clause.
If the problem persists after that, then more detailed examination will be needed.

Indeed, you can invoke asynchronous calls to SQL Server. You can use separate thread to handle the calls to SQL, or you can use built-in support for asynchronous SQL calls. For example, look at SqlCommand.BeginExecuteNonQuery Method at MSDN.

How to implement?

Hi all,

I had many groups of data which column A store number & column B store location. My syntax as below :-

select count(columnA) as no, columnB from table group by columnB

No columnB

20 A

10 B

5 C

How can I select 5 rows for Each ColumnB? (total 15 rows of record)

Thanks!

check out the docs about using TOP.|||TOP is part of it, but it can't get you what you're looking for. I've done it with a cursor before. Create a temp table or table variable. Create a cursor that is each unique value of B. Loop through the cursor inserting into the temp table the top 5 for each B. When the cursor is done, select from your temp table.|||I think that a temp table and a cursor are probably overkill in thissituation. I'd bet that a corrolated subquery would be moreefficient.
Something like this:

SELECT t1.columnA, t1.columnB
FROM table t1
WHERE t1.columnA IN (SELECT TOP 5 t2.columnA FROM table t2 WHERE t2.columnB = t1.columnB)