simple sp

Question

Post reply

simple sp

nazaninahmady_sh

Default port

Points: 1465
More actions
September 11, 2011 at 12:04 am

#243529

Hi
I have two Tables:
Table1:
ID1 Int
FileNumber Int
Table2:
ID2 Int
FileNumber Int
Pic nvarchar(50)
Tag Bit
for each record in table1, i have two records in Table2, for example:
Table1:
ID1------------FileNumber
1--------------100
2--------------200
Table2:
ID2--------FileNumber--------Pic----------Tag
1----------100---------- ----aaaaa-----True
2----------100---------------bbbbb-----Fales
3----------200---------------ccccc-----True
4----------200---------------ddddd-----Fales
i want to write store procedure to have this result:
FileNumber------Pic-------Tag--------Pic----------Tag
100---------- -aaaaa-----True-------bbbb--------Fales
would you please help me?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

matt.bowler SSCrazy Points: 2677 More actions · Answer 1

Are you wanting to pass in file number as a parameter or will the sproc iterate through all file numbers?

nazaninahmady_sh Default port Points: 1465 More actions · Answer 2

thanks alot for your notification.

i want all of filenumbers.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

And what would you like to do if there are more than 2 (or even more) ID2's for a given File Number? Or are you willing to guarantee that no more than two rows will ever appear with the same File Number?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

nazaninahmady_sh Default port Points: 1465 More actions · Answer 4

No, i am sure that in Table2 only i have two records for each "Filenumber"

LutzM SSC Guru Points: 107049 More actions · Answer 5

I guess Jeff asked to decide whether a CrossTab or a DynamicCrossTab would be required.

Both concepts are described in the related links in my signature. Disclaimer: Jeff is the original author, not me 😉

Following please find the "static" CrossTab method (please note how I presented the sample data in a ready to use format...). What I'm wondering though: What would you need table1 for in the given task?

DECLARE @tbl1 TABLE (ID1 INT, FileNumber int)

INSERT INTO @tbl1 VALUES(1,100),(2,200)

DECLARE @tbl2 TABLE(ID2 INT, FileNumber INT, Pic VARCHAR(10), Tag bit)

INSERT INTO @tbl2

VALUES

(1,100,'aaaaa',1),

(2,100,'bbbbb',0),

(3,200,'ccccc',1),

(4,200,'ddddd',0);

WITH cte AS

(

SELECT

FileNumber,

Pic,

CASE WHEN tag =0 THEN 'False' ELSE 'True' END AS Tag,

ROW_NUMBER() OVER(PARTITION BY FileNumber ORDER BY ID2) AS row

FROM @tbl2

)

SELECT

FileNumber,

MAX(CASE WHEN row = 1 THEN Pic ELSE NULL END) AS Pic,

MAX(CASE WHEN row = 1 THEN Tag ELSE NULL END) AS Tag,

MAX(CASE WHEN row = 2 THEN Pic ELSE NULL END) AS Pic,

MAX(CASE WHEN row = 2 THEN Tag ELSE NULL END) AS Tag

FROM cte

GROUP BY FileNumber

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 6

LutzM (9/11/2011)
I guess Jeff asked to decide whether a CrossTab or a DynamicCrossTab would be required.

As usual, spot on, Lutz. Not my job to judge but nicely done with the "preaggregation" and the Cross Tab.

What I'm wondering though: What would you need table1 for in the given task?

Again... I absolutely agree... if FK's are present on Table 2 for Table 1, there's no need to refer to Table 1 at all for this task as it is currently presented.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 7

CELKO (9/11/2011)
There are no magi val universal "id" in RDBMS.

Sure there is... it's called an IDENTITY column and it has several purposes... one is to provide uniqueness in the absense of any reasonable natural key (as in a Customer table) and another is to prevent massive page fragmentation on tables that have rows entered out of the desired order when the column is used with a clustered index. It even works well when there is a natural key but the data is entered out of order according to the key. It also acts as a temporal tie breaker and a good ol' fashioned 1950's style "record keeper" when processing 3rd party data, whose condition is unknown, in a wonderful "scratch" table known as a Temporary Staging Table where data may be cleaned, deduplicated, and validated against other tables before being allowed anywhere near previously validated data.

Besides, SQL Server supposedly doesn't actually meet all of Codd's rules to be an RDBMS... it's really nothing more than a sophisticated file system. 😀

Of course, you being an SQL genius and all, you already know all that, right? 😉

How do you enforce this business rule in the DDL you never bothered to post?

Now THAT's the question of the day. I'd like to hear more about that, as well.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)