Database

2-Jul-10 11:43

Never having addressed this requirement this is a guess only.

I would take a look at the properties in SSMS, I'm sure the tables have size and rowcount in their properties, this means it is stored in a sys table somewhere. It now becomes a search and mathematics problem. Good Luck

Never underestimate the power of human stupidity
RAH

Hello

gertag2-Jul-10 1:08

gertag

2-Jul-10 1:08

Can you help me in doing a query in sql that selects for example products that are registered on a date that is on the last week of month, for each month in a specific year?

Thank you

Re: Hello

Mycroft Holmes2-Jul-10 1:53

Joining columns from two tables into one column

2-Jul-10 1:53

gertag - read the bloody guidelines, you know the horrible yellow sticky messages at the top of the screen.

Use datepart and datediff to identify the last 7 days of the month and use those dates to filter your product registration.

Never underestimate the power of human stupidity
RAH

RossouwDB1-Jul-10 22:05

RossouwDB

1-Jul-10 22:05

Good Day,

I wonder if it is possible to joins two columns from two tables (ie column 1 from table A and column 2 from table B) into one column.

Here are my tables (note that the columns from both the tables have the same names)

TableA

----------------------
|Column1  | Column2  |
----------------------
| aaaa    |  12345   |
----------------------
| bbbp    |  12245   |
----------------------
| ccc     |  12245   |
----------------------

TableB

----------------------
|Column1  | Column2  |
----------------------
| asd     |  12245   |
----------------------
| bbb     |  12245   |
----------------------
| ddd     |  12245   |
----------------------

The first part of my SQL statement combines the two columns of each table into one column in each table, thus
TableA

------------------
|Column3         |
------------------
| aaa : 12345    |
------------------
| bbbp : 12245   |
------------------
| ccc : 12245    |
------------------

TableB

------------------
|Column4         |
------------------
| asd : 12245    |
------------------
| bbb : 12245    |
------------------
| ddd : 12245    |
------------------

Now, what I want to do is to combine these two columns into one column.

The SQL statement below gives me the following result
Result Sigh | :sigh:

------------------------------
| Column 1     | Column2     |
------------------------------
| bbbp : 12245 | asd : 12245 |
------------------------------
| bbbp : 12245 | bbb : 12245 |
------------------------------
| bbbp : 12245 | ddd : 12245 |
------------------------------
| ccc: 12245   | asd : 12245 |
------------------------------
| ccc: 12245   | bbb : 12245 |
------------------------------ 
| ccc: 12245   | ddd : 12245 |
------------------------------

What I want is this : Confused | :confused:

----------------
| Column1      |
----------------
| bbbp : 12245 | 
----------------
| ccc: 12245   |
---------------- 
| asd : 12245  |
---------------- 
| bbb : 12245  |
---------------- 
| ddd : 12245  |
----------------

Here is my SQL statement

SELECT TableA.Column1 + ' : ' + TableA.Column1 AS Column3, TableB.Column1 + ' : ' + TableB.Column2 AS Column4
FROM TableA FULL OUTER JOIN
     TableB ON TableA.Column1 = TableB.Column1
WHERE (TableA.Column1 IN
       (SELECT Column1
        FROM TableA
        WHERE (Column2 = '12245'))) AND (TableB.Column1 IN
                                         (SELECT Column1
                                          FROM TableB
                                          WHERE (Column2 = '12245')))

I've tried all kinds of joins; left outer, right outer, inner, cross. All with the same results.

I would really appreciate your help, as this is kinda urgent! Cool | :cool:

Thanx a lot!
Rossouw
Big Grin | :-D

P.S I am using SQL Server 2005

Re: Joining columns from two tables into one column

David Skelly1-Jul-10 22:32

David Skelly

1-Jul-10 22:32

Well, maybe I'm missing something but I don't see how that query gives the result set that you say it does. You are joining tables together where A.Column1 = B.Column1, but in the result set that you have put here, the first row shows A.Column1 = bbbp and B.Column1 = asd.

Why can't you just use a UNION to get what you want?

Re: Joining columns from two tables into one column

Mycroft Holmes1-Jul-10 22:43

Re: Joining columns from two tables into one column

1-Jul-10 22:43

As David said go with a union, if there are dupes in the result then use distinct.

SELECT TableA.Column1 + ' : ' + TableA.Column1 AS Column3
FROM TableA

UNION
SELECT TableB.Column1 + ' : ' + TableB.Column1 AS Column3
FROM TableB

Never underestimate the power of human stupidity
RAH

RossouwDB1-Jul-10 22:54

RossouwDB

1-Jul-10 22:54

Puurrrrrrfect! Thumbs Up | :thumbsup:

Thanks a lot! (Note, column1 should have been column2), but nevertheless, it works beautifully! Big Grin | :-D

Enjoy the rest of the day
Rossouw

Re: Joining columns from two tables into one column

Mycroft Holmes1-Jul-10 23:19

Re: Joining columns from two tables into one column

1-Jul-10 23:19

Normaly I am reluctant to supply code, I prefer to give hints only - as David did and encourage you to do some research but as you posted some code you were abviously willing to learn.

Look into UNION and UNION ALL as suggested by Jorgen.

Never underestimate the power of human stupidity
RAH

Jörgen Andersson1-Jul-10 23:00

Jörgen Andersson

1-Jul-10 23:00

Mycroft Holmes wrote:
As David said go with a union, if there are dupes in the result then use distinct

Aren't you mixing up UNION with UNION ALL?

UNION is supposed to be distinct while UNION ALL gives you all rows I thought.

"When did ignorance become a point of view" - Dilbert

Re: Joining columns from two tables into one column

Mycroft Holmes1-Jul-10 23:15

replace the value with different text in Select

1-Jul-10 23:15

Jörgen Andersson wrote:
Aren't you mixing up UNION with UNION ALL?

Uhm yes I am Blush | :O

Never underestimate the power of human stupidity
RAH

indian14330-Jun-10 15:30

Re: replace the value with different text in Select

30-Jun-10 15:30

I have a select statement which joins various tables. But there are two tables which specify nearly same thing but values are different. Because of which when joining its giving more rows than expected. Now I want these two tables to be included in join.
like for example QCV_Values table it has Type column as "Oxygen", "NEB" and "Sleep". In another table (arusers) these values are as "HME" and "SLP" in the column "GroupName". "Oxygen" and "NEB" comes under samething "HME".
I need to add these tables in to join to avoid duplicacy in the rows. Can any body please help me in achiving this. Except that everything is working fine.
Can anybody please help me in that.
I am putting my query here
And I have to join the Type with the GroupName, but there is no specific table to relate these two.

SELECT InstanceKey, Category, v.QCDesc, oi.CustCenterName, au.MgrName, oi.InsName1, oi.InsName2, 
oi.InsName3, oi.TherapyId, oi.PhysNum, oi.TherapyIdSlp, oi.PhysNumSlp, SalesPersonName
 FROM dbo.QCV_Instances i
JOIN dbo.vw_OrderIntake oi ON i.InstanceKey=oi.CustNum
--JOIN dbo.Center c ON oi.CustCenterId=c.CenterID 
JOIN dbo.arusers au ON oi.CustCenterId=au.CenterID
JOIN dbo.QCV_Values v ON v.QCID=i.QCValueId

Thanks & Regards,

Md. Abdul Aleem
NIIT technologies

Mycroft Holmes30-Jun-10 17:37

Re: replace the value with different text in Select

30-Jun-10 17:37

You have a serious problem, it's called crappy data!

You either create a CASE in the join that maps each type to another or you create a mapping table that does the same thing. There are pros and cons for both solutions. CASE needs a change to a procedure if there is a new mapping, map table needs maintenance.

Never underestimate the power of human stupidity
RAH

CitrusTech30-Jun-10 19:41

CitrusTech

30-Jun-10 19:41

I concur. But how many times have we all see stuff like this?

I like your mapping table solution, unless this is a one off exercise in which case a case statement will run faster and won't need to be maintained.

Data Quality Solutions

Re: replace the value with different text in Select

indian14330-Jun-10 19:41

Get child table rows in parent column using sql server 2005

30-Jun-10 19:41

Hi,
Yes, I have done it. As my office is far from my house I couldnt inform the same. But your reply is very usefull. Thanks a lot buddy.

Thanks & Regards,

Md. Abdul Aleem
NIIT technologies

alisolution30-Jun-10 8:44

alisolution

30-Jun-10 8:44

hi everybody,

In sql server 2005

I have a parent Table
Person(PersonID,PersonName)
PersonID personName
1 abc
2 xyz.

and child Table
BankAccount(AccountId,PersonId,Bank)

AccountId PersonId Bank
1 1 a
2 1 b
3 1 c
4 2 d
5 2 e

I Want to display result this way.

personId PersonName Bank
1 abc a,b,c
2 xyz d,e

Means display child tables rows result in parent table column.

modified on Wednesday, June 30, 2010 4:19 PM

Re: Get child table rows in parent column

Jörgen Andersson30-Jun-10 9:54

Jörgen Andersson

30-Jun-10 9:54

You fail to mention what database you're using.

In Oracle you can use:

SELECT  personid
       ,personname
       ,LISTAGG(bank, ',') WITHIN GROUP (ORDER BY bank) AS bank
FROM    parent p,child c
WHERE   p.personid = c.personid
GROUP BY personid,personname;

"When did ignorance become a point of view" - Dilbert

Re: Get child table rows in parent column using sql server 2005

i.j.russell30-Jun-10 10:52

i.j.russell

30-Jun-10 10:52

If you want to search for the answer, look at my last reply to the question below.

If you want the answer look at my second reply to indian143 (20:38 28 Jun '10) four questions down.

FOR XML Path is giving error [modified]

indian14329-Jun-10 13:55

29-Jun-10 13:55

Hi All,

Can anybody please help me in executing a query using For Xml Path. Because query with this if I put it in my SQL Server editor, and run it, its giving errors. Is there any specific way to use For Xml Path. Or should I have anything to use it in my SQL Server 2008.
Because any query from any browser is giving errors. What should i do to get rid of it.

One more thing I found in my SQL Server is when I open the "new query" window in my SQL Server and try to write the "FOR XML" I get the RAW and Auto in the intellisense and I dont find the Path mode coming in the intellisense.
Am I miising any dll or any installation process. Because if I write any query using "For Xml Path" not at all compiling.
Can anybody help me in getting this done. I need to use the "For Xml Path", please help me out. Any links or any kind of help is very very welcoming.

Thanks in advance.

Thanks & Regards,

Md. Abdul Aleem
NIIT technologies

modified on Tuesday, June 29, 2010 9:13 PM

Md. Marufuzzaman29-Jun-10 20:15

Md. Marufuzzaman

29-Jun-10 20:15

Could you give us the error detail, transact sql statement as well.

Thanks
Md. Marufuzzaman

I will not say I have failed 1000 times; I will say that I have discovered 1000 ways that can cause failure – Thomas Edison.

i.j.russell29-Jun-10 23:06

i.j.russell

29-Jun-10 23:06

Is your database in 2000 compatibility mode? FOR XML PATH was introduced in 2005.

indian14330-Jun-10 7:34

30-Jun-10 7:34

I have run this exec to change the compatitbility
"EXEC sp_dbcmptlevel Ppsapp, 90". But it is not accepting 90 the "SQL Server 2005" compatibility level. It is giving following error
"Msg 15416, Level 16, State 1, Procedure sp_dbcmptlevel, Line 92
Usage: sp_dbcmptlevel [dbname [, compatibilitylevel]]

Valid values of database compatibility level are 60, 65, 70, or 80.".

Please give me your idea for changing the compatibility level. May be thats why the query is not working.

Thanks & Regards,

Md. Abdul Aleem
NIIT technologies