June 4, 2012 at 2:37 pm
I already have the sp written but it is really ugly, I want to rewrite it, can anyone help?
I have 3 tables,
t1: id division
t2: id, sa
t3: docid, divisionid, sa_id
I want the output should be:
sa_id, division1, division2, .....
1 23 32 nn
2 23 32 nn
3 23 32 nn
4 23 32 nn
Currently I use temp table and create dynamic sql to generate another temp table and then loop t1 and t2 to create the final result.
The whole process is very cumbersome, please do not ask me to post it here. :blush:
I wonder anyone can give me an elegant and simple query to do this job? Thank you in advance.
June 4, 2012 at 2:42 pm
Im not asking you to post the whole SP. But minimum, i need ready-to-use create table scripts, some sample data and clear expected results.
i could sense, you need dynamic cross-tabs for this, but cant confirm unless i see what i asked for.
June 4, 2012 at 2:48 pm
Thanks.
Is there any tricks I can create sample table quickly with sample data in this forum?
June 4, 2012 at 2:53 pm
This sounds like a cross tab.
http://www.sqlservercentral.com/articles/T-SQL/63681/[/url]
http://www.sqlservercentral.com/articles/Crosstab/65048/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 4, 2012 at 2:54 pm
Not something that is handy.. but sample data preparation should not take u more than 15 to 20 mins.
June 4, 2012 at 2:55 pm
Maybe this helps?
CREATE TABLE T1
(
ID int
Division varchar(20)
)
INSERT INTO T1 ( ID, Division ) VALUES ( 1, 'Division1' )
INSERT INTO T1 ( ID, Division ) VALUES ( 2, 'Division2' )
INSERT INTO T1 ( ID, Division ) VALUES ( 3, 'Division3' )
INSERT INTO T1 ( ID, Division ) VALUES ( 4, 'Division4' )
INSERT INTO T1 ( ID, Division ) VALUES ( 5, 'Division5' )
CREATE TABLE T2
(
ID int
SA varchar(20)
)
INSERT INTO T2 ( ID, SA ) VALUES ( 1, 'SA1' )
INSERT INTO T2 ( ID, SA ) VALUES ( 2, 'SA2' )
INSERT INTO T2 ( ID, SA ) VALUES ( 3, 'SA3' )
INSERT INTO T2 ( ID, SA ) VALUES ( 4, 'SA4' )
INSERT INTO T2 ( ID, SA ) VALUES ( 5, 'SA5' )
CREATE TABLE T3
(
DocID int
SA_ID int
DID int
)
INSERT INTO T3 ( DocID, SA_ID, DID ) VALUES ( 1, 1, 1 )
INSERT INTO T3 ( DocID, SA_ID, DID ) VALUES ( 2, 2, 3 )
INSERT INTO T3 ( DocID, SA_ID, DID ) VALUES ( 3, 1, 3 )
INSERT INTO T3 ( DocID, SA_ID, DID ) VALUES ( 4, 3, 2 )
INSERT INTO T3 ( DocID, SA_ID, DID ) VALUES ( 5, 2, 4 )
INSERT INTO T3 ( DocID, SA_ID, DID ) VALUES ( 6, 3, 3 )
INSERT INTO T3 ( DocID, SA_ID, DID ) VALUES ( 7, 1, 5 )
INSERT INTO T3 ( DocID, SA_ID, DID ) VALUES ( 8, 4, 3 )
INSERT INTO T3 ( DocID, SA_ID, DID ) VALUES ( 9, 2, 1 )
INSERT INTO T3 ( DocID, SA_ID, DID ) VALUES ( 10, 4, 3 )
INSERT INTO T3 ( DocID, SA_ID, DID ) VALUES ( 11, 3, 2 )
INSERT INTO T3 ( DocID, SA_ID, DID ) VALUES ( 12, 4, 4 )
INSERT INTO T3 ( DocID, SA_ID, DID ) VALUES ( 13, 1, 3 )
INSERT INTO T3 ( DocID, SA_ID, DID ) VALUES ( 14, 5, 5 )
INSERT INTO T3 ( DocID, SA_ID, DID ) VALUES ( 15, 5, 3 )
June 6, 2012 at 7:08 am
What exact output do you expect from setup provided?
June 6, 2012 at 12:03 pm
I have created the sp myself, like a PP said, there is no handy and easy solution.
Thanks anyway.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply