October 12, 2011 at 2:33 am
Hi all,
I have two tables one having company data named COMPANY and other have Landmarks data named LANDMARK.
COMPANY have two fields(name and keywords) and LANDMARK (area,landmarkName)
i want to select the whole data from both tables as one word/column.
just like this shows me
select name from COMPANY
how i can do?
October 12, 2011 at 2:39 am
This question is not detailed enough.
Please provide sample data, showing source data and desired output, as described in the link in my signature.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 12, 2011 at 2:52 am
engrshafiq4 (10/12/2011)
Hi all,I have two tables one having company data named COMPANY and other have Landmarks data named LANDMARK.
COMPANY have two fields(name and keywords) and LANDMARK (area,landmarkName)
i want to select the whole data from both tables as one word/column.
just like this shows me
select name from COMPANY
how i can do?
Hello and welcome to SSC!
It seems that your DDL script seems to have become detached from your post, or perhaps you were unaware of the benefits of providing one.
When you have time, please read this article[/url] about the best way to provide us with working sample data and DDL scripts. This will allow the unpaid volunteers of this site to provide you with working, tested code for your particular problem.
For now, I've knocked up what I've interpreted from your description: -
--First, lets create some sample data to play with
IF object_id('tempdb..#COMPANY') IS NOT NULL
BEGIN
DROP TABLE #COMPANY
END
IF object_id('tempdb..#LANDMARK') IS NOT NULL
BEGIN
DROP TABLE #LANDMARK
END
--50,000 Random rows of data
SELECT TOP 50000 IDENTITY(INT,1,1) AS CompanyId,
LEFT(CONVERT(VARCHAR(255), NEWID()),50) AS Name,
SUBSTRING(CONVERT(VARCHAR(255), NEWID()), 1, ABS(CAST(NEWID() AS binary(6)) % 25) + 1)+','+
SUBSTRING(CONVERT(VARCHAR(255), NEWID()), 1, ABS(CAST(NEWID() AS binary(6)) % 25) + 1) AS Keywords
INTO #COMPANY
FROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2, master.dbo.syscolumns sc3
--Add a Primary Key
ALTER TABLE #COMPANY
ADD CONSTRAINT COMPANY_PK_CompanyId
PRIMARY KEY CLUSTERED (CompanyId) WITH FILLFACTOR = 100
--1,000 Random rows of data
SELECT TOP 1000 IDENTITY(INT,1,1) AS LandmarkId,
LEFT(CONVERT(VARCHAR(255), NEWID()),50) AS Area,
LEFT(CONVERT(VARCHAR(255), NEWID()),50) AS LandmarkName
INTO #LANDMARK
FROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2
--Add a Primary Key
ALTER TABLE #LANDMARK
ADD CONSTRAINT LANDMARK_PK_LandmarkId
PRIMARY KEY CLUSTERED (LandmarkId) WITH FILLFACTOR = 100
So, using my sample data above, if we wanted to get all of the data displayed together we're lucky because the data-types are the same.
All we need to do is UNION the results together: -
SELECT Name AS AllData FROM #COMPANY
UNION ALL
SELECT Keywords FROM #COMPANY
UNION ALL
SELECT Area FROM #LANDMARK
UNION ALL
SELECT LandmarkName FROM #LANDMARK
We're expected a result set of 102,000 (50,000 names + 50,000 keywords + 1,000 areas + 1,000 landmarknames) and that's what we get.
If this isn't what you were after, please follow the previous advise and post DDL and readily consumable test data along with expected results.
Thanks!
October 12, 2011 at 2:53 am
IF i run this QUERY
select top 3 COMPANY,Keywords from COMPANIES
give me the following
COMPANY|Keywords
A | Q
B | W
C | E
NOw if i run this query
select top 3 landmark,area from LANDMARK
it five me the following
landmark|area
D | R
F | T
G | Y
Now i want to get like this
A
B
C
Q
W
E
D
F
G
R
T
Y
How i will get this result..hope now you will easily understand what i want
October 12, 2011 at 3:08 am
Would this work:
SELECT name FROM company
UNION ALL
SELECT keywords FROM company
UNION ALL
SELECT area FROM landmark
UNION ALL
SELECT landmarkName FROM landmark
Or does the ordering of the data within the one column differ from this?
October 12, 2011 at 3:10 am
Yes this worked for me.....thanks alot
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply