Query to display result in column 1

[angjbsca]

HI there..I have a query who take several records I was wondering if I can display the result in 6 column instead of 2..

then query select f1,f1 from t1

result f1, f2
113,343
232,323
....
....300 rows.

I want f1,f2,f1contin2,f2conti2,f1conti3,f2conti3
....100 rows

 

[gmmastros]

You could insert the data in to a table variable with an identity column and then join it to itself a couple times. Here's how.

[green]-- Dummy data[/green]
Declare @Temp Table(f1 integer, f2 integer)

Insert Into @Temp Values(1,2)
Insert Into @Temp Values(3,4)
Insert Into @Temp Values(5,6)
Insert Into @Temp Values(7,8)
Insert Into @Temp Values(9,10)
Insert Into @Temp Values(11,12)
Insert Into @Temp Values(13,14)
Insert Into @Temp Values(15,16)
Insert Into @Temp Values(17,18)
Insert Into @Temp Values(19,20)

[green]-- The query[/green]

Declare @Data Table(RowId Integer Identity(1,1), f1 integer, F2 Integer)

Insert Into @Data(f1, f2) Select F1, F2 From @Temp

Select A.F1, 
       A.F2, 
       B.F1 As SecondF1, 
       B.F2 As SecondF2, 
       C.F1 As ThirdF1, 
       C.F2 As ThirdF2
From   @Data A
       Inner Join @Data B On A.RowId = B.RowId - 1
       Inner Join @Data C On A.RowId = C.RowId - 2
Where  A.RowId % 3 = 1

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[angjbsca]

Great!!! one thing. tell me the query logic
with these joins and expecialy the where clause.

 

[gmmastros]

OK.

<teaching mode>

Do you see the part that says Integer Identity(1,1). That part creates a field in the table that is an auto number field, so each record has an ID the increments as it goes along.

If you do Select * From @Data, you would see...

[tt][blue]
RowId f1 f2
1 1 2
2 3 4
3 5 6
4 7 8
5 9 10
6 11 12
7 13 14
8 15 16
9 17 18
10 19 20[/blue][/tt]

The From clause is joining to the same table 3 times. Each time, it's joining on RowId. Look at the table above. You want the fields from rowid=1 joined with the fields from rowid = 2 and rowid = 3. That's where the A.RowId = B.RowId - 1 and A.RowId = C.RowId - 2 are coming from.

And now for the where clause.

The % operator is a modulus operator. Meaning... it returns the remainder. For example we know that 10/3 = 3.333333, or 10 and [!]1[/!]/3. That part in red is the part returned by %.

Without the where clause you would get this....

[tt][blue]
A.RowId F1 F2 SecF1 SecF2 ThirdF1 ThirdF2
1 1 2 3 4 5 6
2 3 4 5 6 7 8
3 5 6 7 8 9 10
4 7 8 9 10 11 12
5 9 10 11 12 13 14
6 11 12 13 14 15 16
7 13 14 15 16 17 18
8 15 16 17 18 19 20
[/blue][/tt]

Notice that it's every 3rd row you want, row 1, 4, and 7.

1 % 3 = 1, 4 % 3 = 1 and 7 % 3 = 1

Now that I am looking at this a little closer, I realize that the original query I showed should have LEFT JOIN instead of INNER JOIN. If you use INNER JOIN, you are missing some records.

</teaching mode>

I hope this clearly explains what the query is doing. If not, let me know and I will explain some more.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[jbenson001]

Awesome code George!!

 

[gmmastros]

Thanks jbenson001.

I always appreciate when someone clicks the 'Thank gmmastros for this valuable post!' link.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[jbenson001]

No problem.. I appreciate great coding tips that help me learn new things!

 

[angjbsca]

Thanks a lot....I appreciated your time.