SQL Server Another simple question

Question

I have 2 temp Tables [Description] and [Institution], I want to have these two in one table.

They are both tables that look like this:

Table1; #T1

|Description|
blabla
blahblah
blagblag

Table2; #T2

|Institution|
Inst1
Inst2
Inst3

I want to get it like this:

Table3; #T3

    |Description|     |Institution|
    blabla             Inst1
    blahblah           Inst2
    blagblag           Inst3

They are already in sort order.

I just need to get them next to each other..

Last time I asked was something almost the same.

I used this query

Create Table #T3
( 
   [From] Datetime
   ,[To] Datetime
)

INSERT INTO #T3  
  SELECT #T1.[From]       
         , MIN(#T2.[To])
   FROM #T1   
   JOIN #T2 ON #T1.[From] < #T2.[To]   
   GROUP BY #T1.[From] 

Select * from  #T3

It did work for the date values, but it won't work here ? :s

Thank you.

Is blagblag in some way connected to Inst3 or is it OK to pair blagblag with Inst2 instead? — Mikael Eriksson
– Mikael Eriksson, Commented Jul 14, 2011 at 12:33
They are not connected, i need to get the values just next to eachother in different columns. Because i have ordered them before in a query, i dont need another order by, or join eachother on Id — Gigli
– Gigli, Commented Jul 14, 2011 at 12:36
"They are already in Sortorder" - they aren't. They really aren't. Tables don't have an order - that's one of the basic truths of SQL. Whilst simple tests with a few rows may suggest, e.g. that the results return in the same order in which they were inserted, that is by no means guaranteed. The only way to guarantee a particular order is to use ORDER BY - and in it's simplest form, that needs to be given a column by which to sort the rows. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Jul 14, 2011 at 12:37
You need a column in #T1 and in #T2 either to join the rows that should be together or something that can be used in an order by. — Mikael Eriksson
– Mikael Eriksson, Commented Jul 14, 2011 at 12:43

JohnD · Accepted Answer · 2011-07-14 13:04:36Z

One thing that concerns me is that you say that the values "are already in sort order". There really is no default sort order -- if you don't specify a sort order, you are at the mercy of SQL Server to determine the order in which the data is returned. The solution below assumes that there is some way to sort the data such that the records "match up" (using the ORDER BY clauses).

Hope this helps,

John

-- Table 1 test data
Create Table #T1
(
   [Description] nvarchar(30)
)
INSERT INTO #T1 ([Description]) VALUES ('desc1')
INSERT INTO #T1 ([Description]) VALUES ('desc2')
INSERT INTO #T1 ([Description]) VALUES ('desc3')

-- Table 2 test data
Create Table #T2
(
   [Institution] nvarchar(30)
)
INSERT INTO #T2 (Institution) VALUES ('Inst1')
INSERT INTO #T2 (Institution) VALUES ('Inst2')
INSERT INTO #T2 (Institution) VALUES ('Inst3')

-- Create table 3
Create Table #T3
( 
   [Description] nvarchar(30),
   [Institution] nvarchar(30)
);


-- Use CTE2 to add row numbers to the data; use the row numbers to join the tables
-- you must specify the sort order for the data in the tables
WITH CTE1 (Description, RowNum) AS
(
    SELECT [Description], ROW_NUMBER() OVER(ORDER BY [Description]) as RowNum
    FROM #T1
),
CTE2 (Institution, RowNum) AS
(
    SELECT Institution, ROW_NUMBER() OVER(ORDER BY Institution) as RowNum
    FROM #T2
)
INSERT INTO #T3
SELECT CTE1.Description, CTE2.Institution 
FROM CTE1
LEFT JOIN CTE2 ON CTE1.RowNum = CTE2.RowNum


Select * from  #T3

Collectives™ on Stack Overflow

SQL Server Another simple question

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related