Collation problem: combing

Showing posts with label combing. Show all posts

Sunday, March 25, 2012

Combining multiple columns into one column.

Combing multiple columns like [LastName],[FirstName] and
[MiddleName]into one column named as [Name] is very simple in Access,
but how will i do that in SQL? Any suggestions? PLease?
Thanks in advance,
Geri
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!You have to add another column, update with existing data,
and then drop existing columns.
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Geri Gavertz" <gerific@.yahoo.com> wrote in message
news:ezPCuV8GFHA.1396@.TK2MSFTNGP10.phx.gbl...
> Combing multiple columns like [LastName],[FirstName] and
> [MiddleName]into one column named as [Name] is very simple in Access,
> but how will i do that in SQL? Any suggestions? PLease?
> Thanks in advance,
> Geri
>
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!|||same as in Access
Select LastName+' ' + FirstName + ' ' + FirstName as Name from Table
Madhivanan|||same as in Access
Select LastName+' ' + FirstName + ' ' + MiddleName as Name from Table
Madhivanan|||<madhivanan2001@.gmail.com> wrote in message
news:1109400766.869281.200560@.o13g2000cwo.googlegroups.com...
> same as in Access
> Select LastName+' ' + FirstName + ' ' + FirstName as Name from Table
> Madhivanan
>
You might want to wrap them in IsNull so that a NULL in one of the columns
doesn't NULL out the entire result:
Select IsNull(FirstName, '') + ' ' + IsNull(MiddleName, '') + ' ' +
IsNull(LastName, '') As FullName from MyTable
Daniel Wilson
Senior Software Solutions Developer
Embtrak Development Team
http://www.Embtrak.com
DVBrown Company

Thursday, March 22, 2012

Combing Multiple Databases for Ad Hoc Reporting

Hey all. Sorry to ask such a basic question, but I am needing to combine data from about 3 different databases into 1 ad hoc report. I know that you can include multiple datasets, but I need this to act as 1 dataset so that the table data between all databases are related to one another.

I know very little about the potential of analysis services / integration services, so I don't know if they contain the answer to my problem.

Does anyone have a suggestion of how I should approach this problem (as always, speed is a bit of a consideration here as well).

Thanks!

Are those 3 db's on different servers/instances?

If yes, one way is to create linked servers to them, and do a BIG join of the 3 db's

say SELECT * FROM DB1, Server2.DB.dbo.table, Server3.DB.dbo.table WHERE blah blah

you get the idea, use the fully quantified names SERVER_NAME.DB_NAME.OWNER.TABLE_NAME

If they're on the same server/instance, then you can do the same, but omit the SERVER_NAME part and linked server part

That's my suggestion as I can't think of any good ways

|||

Yeah,

My first thought was to do that, but I guess I was hoping for a cleaner way to go about it. Some of these databases are quite large, so I worry about performance.

I have been tasked w/ creating ad hoc reporting that spans these databases "where ppl can report on anything", which seems a bit unrealistic considering the size of the databases involved.

I know that OLAP methods can reduce query speeds drastically, but my problem is that I know so very little about it. I guess my hope was making 3 cubes and linking them as a single datasource...

Thanks for the reply and I guess i will start querying... lol.

Nathan

|||

Hi,
Did you try the option of building OLAP cube from those 3 DBs and then generating a Report Model out of the cube. I think that is a clear option.
Thanks,
S Suresh

|||I am very new to the OLAP scene, although I am trying ramp up very quickly. I bought the Professional SQL server - Analysis Services 2005 w/ MDX and I am working through that and learning a decent amount (about 60% of the book is "point here and click" and does not explain much of the logic behind). My biggest question though is how to make a cube that spans 3 databases... Do I make 3 individual cubes and then make a cube to report on those 3 cubes? I have not found a place in the "wizards" where I can specify more than 1 datasource for a cube (or maybe I should just use the code-behind?). Does anyone have a good link for this kind of reporting or experience with this issue? Thanks!|||

I have a similar situation. Many copies of the same database structure (1000+) on multiple servers (7).

I need to run reports that reference data in all of the databases or selected groups of databases. Is this candidate for OLAP? We currently create queries using linked servers but as you can imagine - they take forever to run depending on the number of databases included.

We have just started to explore possible solutions. The real problem is that the reports need to run against real time data so I am assuming that whatever solution is proposed, it will have to connect to all of the current databases.

Any suggestions or comments would be appreciated!!

Michael

|||

Has anyone had this situation? I would have thought that it would have been common with large retail type organizations. If I had 200 retail locations around the country all running local databases of inventory but I wanted to be able to query all of them at anytime for current inventory status - how would I do it? I need the solution to be real time and not take a long time for the result set.

Thanks

Combing Multiple Databases for Ad Hoc Reporting

I know very little about the potential of analysis services / integration services, so I don't know if they contain the answer to my problem.

Does anyone have a suggestion of how I should approach this problem (as always, speed is a bit of a consideration here as well).

Thanks!

Are those 3 db's on different servers/instances?

If yes, one way is to create linked servers to them, and do a BIG join of the 3 db's

say SELECT * FROM DB1, Server2.DB.dbo.table, Server3.DB.dbo.table WHERE blah blah

you get the idea, use the fully quantified names SERVER_NAME.DB_NAME.OWNER.TABLE_NAME

If they're on the same server/instance, then you can do the same, but omit the SERVER_NAME part and linked server part

That's my suggestion as I can't think of any good ways

|||

Yeah,

My first thought was to do that, but I guess I was hoping for a cleaner way to go about it. Some of these databases are quite large, so I worry about performance.

I have been tasked w/ creating ad hoc reporting that spans these databases "where ppl can report on anything", which seems a bit unrealistic considering the size of the databases involved.

I know that OLAP methods can reduce query speeds drastically, but my problem is that I know so very little about it. I guess my hope was making 3 cubes and linking them as a single datasource...

Thanks for the reply and I guess i will start querying... lol.

Nathan

|||

Hi,

Did you try the option of building OLAP cube from those 3

DBs and then generating a Report Model out of the cube. I think that is

a clear option.

Thanks,

S Suresh

I have a similar situation. Many copies of the same database structure (1000+) on multiple servers (7).

Any suggestions or comments would be appreciated!!

Michael

|||

Thanks

Combing Multiple Databases for Ad Hoc Reporting

I know very little about the potential of analysis services / integration services, so I don't know if they contain the answer to my problem.

Does anyone have a suggestion of how I should approach this problem (as always, speed is a bit of a consideration here as well).

Thanks!

Are those 3 db's on different servers/instances?

If yes, one way is to create linked servers to them, and do a BIG join of the 3 db's

say SELECT * FROM DB1, Server2.DB.dbo.table, Server3.DB.dbo.table WHERE blah blah

you get the idea, use the fully quantified names SERVER_NAME.DB_NAME.OWNER.TABLE_NAME

If they're on the same server/instance, then you can do the same, but omit the SERVER_NAME part and linked server part

That's my suggestion as I can't think of any good ways

|||

Yeah,

My first thought was to do that, but I guess I was hoping for a cleaner way to go about it. Some of these databases are quite large, so I worry about performance.

I have been tasked w/ creating ad hoc reporting that spans these databases "where ppl can report on anything", which seems a bit unrealistic considering the size of the databases involved.

I know that OLAP methods can reduce query speeds drastically, but my problem is that I know so very little about it. I guess my hope was making 3 cubes and linking them as a single datasource...

Thanks for the reply and I guess i will start querying... lol.

Nathan

|||

Hi,

Did you try the option of building OLAP cube from those 3

DBs and then generating a Report Model out of the cube. I think that is

a clear option.

Thanks,

S Suresh

I have a similar situation. Many copies of the same database structure (1000+) on multiple servers (7).

Any suggestions or comments would be appreciated!!

Michael

|||

Thanks

sql sql

combing cloumns

I am having one table.

in that columns are trid and remarks.

the data is:

trid | remarks

-------

1 travel:comment1

1 travel:comment2

1 emp:emp comment1

1 emp:emp comment2

so i want to display query like in this below format:having 3 columns,i.e trid, travel remarks, empremarks and travelremarks should display data start with 't' , empremarks data should display data starts with 'e'.

I am getting data start with 't' and 'e',but how to combine travel data, emp data. and it should display in one row itself instead of 4 rows for every trid '1'.

trid | travelremarks | emp remarks

-------------------------------------------

1 travel:comment1,travel:comment2 emp:emp comment1,emp:emp comment2

please help me.

Thanks,

swapprose.

SQL Server 2005 solution:

SELECT pvt.trid, pvt.traas'travelremarks', pvt.empas'emp remarks'FROM

(SELECT pt.trid, pt.remarks_left3, pt.[1]+coalesce(','+ pt.[2],'')as Merged

from(SELECT trid,LEFT(remarks, 3)AS remarks_left3, remarks, ROW_NUMBER()OVER(PARTITIONBYLEFT(remarks, 3)ORDERBY remarks)as seqFROM yourTable_2)as t

PIVOT(min(remarks)FOR seqin([1], [2]))as pt)as t1

PIVOT(MIN(t1.Merged)FOR t1.remarks_left3IN([tra], [emp]))AS pvt

|||

Thank you very much.

but i want query in sql server 2000.

its giving error like rownumber() not existing.

so can u plz make query in sql server 2000.

and the data which i gave was only sample data.

i think u done query by considering only 4 rows .i want in generalised way.

i am not getting where r u finding out travel remarks column should display data starts with 't'

and emp rmarks column data should display data starts with 'e'. b'coz i want to split remarks into travel and emp remarks given by travel and emp's.

i want query in a generalised way,b'coz table contains so many trid's ,so many travel remarks,emp

remarks data.

the table is like,just i m giving sample data.so it contains so many trid's ,remarks

trid | remarks

---------

1 travel:option given
1 emp :some comments
2 emp :trid select
2 travel:chosen
1 emp :option1
3 travel:set id
4 emp :choose
5 emp :abc

so i want in generalised way. the order of remarks may come in any way.
please consider all my points.
i m waiting for ur reply.

Thanks,

swapprose.

|||

try this one for sql server 2000:

SELECT t3.trid,max(case t3.remarks_left3when'emp'then t3.remarksend)as emp_category

,max(case t3.remarks_left3when'tra'then t3.remarksend)as travel_category

FROM(select tr.trid, tr.remarks_left3,max(case tr.seqwhen 1then tr.remarksend)

+max(case tr.seqwhen 2then', '+ tr.remarkselse''end)

+max(case tr.seqwhen 3then', '+ tr.remarkselse''end)

+max(case tr.seqwhen 4then', '+ tr.remarkselse''end)

+max(case tr.seqwhen 5then', '+ tr.remarkselse''end)as remarks

from

(select trid,LEFT(remarks, 3)AS remarks_left3, remarks,

(selectcount(*)from yourTable_2as t2

whereLEFT(t2.remarks, 3)=LEFT(t1.remarks, 3)

AND t2.remarks<= t1.remarks)as seqfrom yourTable_2AS t1)as tr

groupby tr.trid, tr.remarks_left3)AS t3

GROUPBY t3.trid

|||

Thank you very much for ur solution.When my table is having only trid no. 1 , its coming properly.

But the thing is when my table is having trid's like 1,2,3,4, then its not working.

when i tried by using trid's no. 1,then its coming.Fine.

if i have trid no's 1,2,3,4 its not coming.

i want in generalised way.plz assume table is having so many trid's,remarks and so many rows.

suppose my table is having data like:

trid remarks

--------
1 emp:aa
1 emp:bb
1 travel:cc
1 travel:dd
2 travel:ee
2 travel:ff
2 travel:gg
2 emp:hh
3 emp:ftgfdg
3 emp:cvgxcv
4 travel:rrtt

can you plz try it once by table having trid's like 1,2,3-- so on.
plz help me.
i m waiting for ur solution.
thanks a lot for ur immediate reply and help.

|||

Hi,

Show the exact result set you want for your sample data set. Do you combine all ids together for one big row?

|||

I'll explain my problem clearly.

assume this is my table having sample data.

trid remarks

--------
1 emp:aa
1 emp:bb
1 travel:cc
1 travel:dd

this works fine .result is like

but when i include data in table like below:

trid travel remarks emp remarks

1 travel:cc,travel:dd emp:aa,emp:bb

--------------------------------------------------------------

when i included more trid's,its not working

trid remarks

--------
1 emp:aa
1 emp:bb
1 travel:cc
1 travel:dd
2 travel:ee
2 travel:ff
2 travel:gg
2 emp:hh
3 emp:ftgfdg
3 emp:cvgxcv
4 travel:rrtt

now table contains trid's like 1,2,3,4 ,then result is not proper.

result is SHOWING like:

trid travel remarks emp remarks

1 travel:cc,travel:dd emp:aa,emp:bb

2 NULL NULL

3 NULL NULL

4 NULL NULL

could u plz tell me the solution.

bye

swapprose.

|||

Here are the fixed solutions for both SQL Server 2000 and 2005:

--SQL Server 2000

SELECT t3.trid,max(case t3.remarks_left3when'emp'then t3.remarksend)as emp_category

,max(case t3.remarks_left3when'tra'then t3.remarksend)as travel_category

FROM(select tr.trid, tr.remarks_left3,max(case tr.seqwhen 1then tr.remarksend)

+max(case tr.seqwhen 2then', '+ tr.remarkselse''end)

+max(case tr.seqwhen 3then', '+ tr.remarkselse''end)

+max(case tr.seqwhen 4then', '+ tr.remarkselse''end)

+max(case tr.seqwhen 5then', '+ tr.remarkselse''end)as remarks

from

(select trid,LEFT(remarks, 3)AS remarks_left3, remarks,

(selectcount(*)from yourTable_2as t2

where t1.trid=t2.tridANDLEFT(t2.remarks, 3)=LEFT(t1.remarks, 3)

AND t2.remarks<= t1.remarks)as seqfrom yourTable_2AS t1)as tr

groupby tr.trid, tr.remarks_left3)AS t3

GROUPBY t3.trid

--SQL Server 2005

SELECT pvt.trid, pvt.traas'travelremarks', pvt.empas'emp remarks'FROM

(SELECT pt.trid, pt.remarks_left3, pt.[1]+coalesce(','+ pt.[2],'')as Merged

from(SELECT trid,LEFT(remarks, 3)AS remarks_left3, remarks, ROW_NUMBER()OVER(PARTITIONBY trid,LEFT(remarks, 3)ORDERBY remarks)as seqFROM yourTable_2)as t

PIVOT(min(remarks)FOR seqin([1], [2]))as pt)as t1

PIVOT(MIN(t1.Merged)FOR t1.remarks_left3IN([tra], [emp]))AS pvt

--The result is:

1 travel:cc,travel:dd emp:aa,emp:bb
2 travel:ee,travel:ff emp:hh
3 NULL emp:cvgxcv,emp:ftgfdg
4 travel:rrtt NULL

|||

Thanks a lot.

Now its working fine for everything.

if u don't mind can u explain me logic.

once again Thank you very much for your immediate help.

Bye,

swapprose.

|||

suppose my table is having data like.

trid pnrno isoptionchosen
------------------
1 100 null
1 101 1
1 102 null
2 200 1
2 201 null

i want query like below.

--------------------------------------------------------------
100 null 101 1 102 null 101 1
200 1 201 null null null 200 1

so i want to display fields from same table accroding to trid's.
first trid='1' : it is having 3 rows,that 3 rows data should display and last isoptionchosen=1 that row data should display.
for trid=2 :its is having 2 rows.that 2 rows data should display and whatever ischosenoption=1 that row should display

Please help me.

Thanks,

bye

swapprose.

|||

SELECT tr.trid,max(case tr.seqwhen 1then tr.pnrnoend)AS pnrno1,max(case tr.seqwhen 1then tr.isoptionchosenend)AS isoptionchosen1,

max(case tr.seqwhen 2then tr.pnrnoend)AS pnrno2,max(case tr.seqwhen 2then tr.isoptionchosenend)AS isoptionchosen2,

max(case tr.seqwhen 3then tr.pnrnoend)AS pnrno3,max(case tr.seqwhen 3then tr.isoptionchosenend)AS isoptionchosen3,

max(case tr.isoptionchosenwhen 1then tr.pnrnoend)AS Chosenpnrno, 1AS Chosenisoptionchosen

FROM(SELECT t2.trid, t2.pnrno, t2.isoptionchosen,(selectcount(*)from yourTable_2as t1where t1.trid=t2.tridAND t1.pnrno<= t2.pnrno)as seq

FROM(select trid, pnrno, isoptionchosenfrom yourTable_2) t2) tr

GROUPBY tr.trid

-- Or

SELECT tt.trid, tt.pnrno1, tt.isoptionchosen1, tt.pnrno2, tt.isoptionchosen2, tt.pnrno3, tt.isoptionchosen3, t4.chosenpnrno, t4.chosenisoptionchosenFROM(SELECT tr.trid,max(case tr.seqwhen 1then tr.pnrnoend)AS pnrno1,max(case tr.seqwhen 1then tr.isoptionchosenend)AS isoptionchosen1,

max(case tr.seqwhen 2then tr.pnrnoend)AS pnrno2,max(case tr.seqwhen 2then tr.isoptionchosenend)AS isoptionchosen2,

max(case tr.seqwhen 3then tr.pnrnoend)AS pnrno3,max(case tr.seqwhen 3then tr.isoptionchosenend)AS isoptionchosen3

FROM(SELECT t2.trid, t2.pnrno, t2.isoptionchosen,

(selectcount(*)from yourTable_2as t1where t1.trid=t2.tridAND t1.pnrno<= t2.pnrno)as seq

FROM(select trid, pnrno, isoptionchosenfrom yourTable_2) t2) tr

GROUPBY tr.trid) tt

INNERJOIN

(SELECT trid, pnrnoas chosenpnrno, isoptionchosenas chosenisoptionchosenFROM yourTable_2WHERE isoptionchosen=1)AS t4

ON t4.trid=tt.trid

|||

Thanks a lot.

i have one more query.i.e

can u show seq.number like 1,2 ,3 for this result.

Thanks,

swapprose.

|||

SELECT tr.trid,max(case tr.seqwhen 1then tr.pnrnoend)AS pnrno1,max(case tr.seqwhen 1then tr.isoptionchosenend)AS isoptionchosen1, 1as seq1,

max(case tr.seqwhen 2then tr.pnrnoend)AS pnrno2,max(case tr.seqwhen 2then tr.isoptionchosenend)AS isoptionchosen2, 2as seq2,

max(case tr.seqwhen 3then tr.pnrnoend)AS pnrno3,max(case tr.seqwhen 3then tr.isoptionchosenend)AS isoptionchosen3, 3as seq3,

max(case tr.isoptionchosenwhen 1then tr.pnrnoend)AS Chosenpnrno, 1AS Chosenisoptionchosen

FROM(SELECT t2.trid, t2.pnrno, t2.isoptionchosen,(selectcount(*)from yourTable_2as t1where t1.trid=t2.tridAND t1.pnrno<= t2.pnrno)as seq

FROM(select trid, pnrno, isoptionchosenfrom yourTable_2) t2) tr

GROUPBY tr.trid

|||

Thanks for ur reply.

but my question is something different.

suppose my table is having data like

empname course

-----------

john m.s

rechel b.e

jack p.g

so i wnat sequence no. for these rows.

sequence no. empname course

-----------------

1 john m.s

2 recehl b.e

3 jack p.g

Thanks.

swapprose.

Combing 2 Queries into 1

I have spent several hours hurting my brain tring to solve the following. I am hoping one of you SQL gurus can help me out.

I have two tables (each with two fields):

Group with GroupName and GroupDescription

Here is some sample data:

HR HumanResources
IT Information Technology
Boston BostonOffice
NJ NewJerseyOffice

GroupMember with GroupName and UserID

Here is some sample data:

IT CMessineo
NJ CMessineo
Boston JSmith
IT JSmith

What I want is a single stored procedure that when passed a UserID will return a result set that lists all the groups and a 1 or ) if the UserID is a member of that group.

For example calling the procedure and passing CMessineo would produce this result:

HR 0
IT 1
Boston 0
NJ 1

Can this be done in a stored procedure?

This is what I have so far, but I am in over my head:

(
@.UserID varChar(40)
)
As
SELECT"GROUP".GROUPNAME, (SELECT ?
FROM"Group", GroupMember
Where"Group".GroupName = GroupMember.GroupName and GroupMember.UserID = @.UserID)

FROM"GROUP"

Thanks in advance,

ChrisI figured it out - it required an outer join (my first):

SELECT"Group".GroupName, COUNT(UserID) AS MEMBER
FROM"Group" Left Outer Join GroupMember
ON"Group".GroupName = GroupMember.GroupName and GroupMember.UserID=@.UserID
GROUP BY"Group".GroupName

Sunday, March 25, 2012

Combining multiple columns into one column.

Thursday, March 22, 2012

Combing Multiple Databases for Ad Hoc Reporting

Combing Multiple Databases for Ad Hoc Reporting

Combing Multiple Databases for Ad Hoc Reporting

combing cloumns

Combing 2 Queries into 1

Collation problem

Blog Archive

About Me