importing partials datas from 2 tables in another one

I am having a problem to get all datas into one table
I must get datas from 2 other tables
datas from the first table are columns
but datas of the second table are rows

I cannot change the structure of TableA because an other application is using it as it is

TableA
num | value | key
1 | a | 1400
2 | b | 1401
3 | c | 1402
4 | d | 1403
...|... | 1403

TableB
key | name | descr | value
400 | john | ok | 451

TableC (the table where I want to insert datas from Table1 and Table2)

val1 | val2 | val3 | val4 |name | descr | value
1 | 2 | 3 |4 | john | ok | 451

TableA has thousands of rows and different numbers
I must get 8 different datas from TableA

so I get an horrible Query !

SELECT

TableB.name, TableB.descr, TableB.value,
TableA_1.value AS val1,
TableA_2.value AS val2,
TableA_3.value AS val3,
TableA_4.value AS val4,
TableA_5.value AS val5,
TableA_6.value AS val6,
TableA_7.value AS val7,
TableA_8.value AS val8

FROM dbo.TableB INNER JOIN
dbo.TableA_1 ON TableB.key = TableA_1.key INNER JOIN
dbo.TableA_2 ON TableB.key = TableA_2.key INNER JOIN
dbo.TableA_3 ON TableB.key = TableA_3.key INNER JOIN
dbo.TableA_4 ON TableB.key = TableA_4.key INNER JOIN
dbo.TableA_5 ON TableB.key = TableA_5.key INNER JOIN
dbo.TableA_6 ON TableB.key = TableA_6.key INNER JOIN
dbo.TableA_7 ON TableB.key = TableA_7.key INNER JOIN
dbo.TableA_8 ON TableB.key = TableA_8.key

WHERE TableA_1.num = 145
AND TableA_2.num = 80
AND TableA_3.num = 3160
AND TableA_4.num = 41
AND TableA_5.num = 50
AND TableA_6.num = 51
AND TableA_7.num = 53
AND TableA_8.num = 56

how can i do it in the best way ?

Thank youUse a CROSSTAB query and you can do this with a single call to your table. Lookup CROSSTAB in Books Online and you will see an excellent example of how to do this using CASE statements.|||I had a look on line and i cannot find any exemple of what i need

I wan that 2 lines from one table and 1 column from another one
become 3 colomns in a third one

if anyone has a link it will be great ?|||CROSSTAB in Books Online (installed with SQL Server).sql

importing datas into MS SQL serveur

hello !

I am a student in a compagny because its my training. But, I must use MS SQL server and i dont know at all this software !
I must import datas , which are in a ascii form, into MS SQL serveur 7,0.

Can you give me further information please, because its not easy at all !
thanksYou can use DTS wizard to import data from a text file or other heterogenous data sources.

Refer to books online and the following articles about DTS:
http://www.databasejournal.com/features/mssql/article.php/3325731
http://www.devasp.net/net/search/redirect.asp?sid=927
http://www.devasp.net/net/search/redirect.asp?sid=2671
http://www.sqldts.com

Importing data from a text file with quotes

Dear all,

I trying to import a set of datas from a text file to a table call Movement.

Table structure for Movement is as following:

1, JobNo, Character (10)
2, Date, smalldatetime
3, ItemDesc, Character (100)
4, StaffID, Character (5)
5, Quantity, Decimal(10,4)
6, Completed,Bit

text file sample contains:

"T200601100";1/10/2006;"A3 papers, Plastic covers","T1001",500,1
"T200601101";1/11/2006;"Ink Refiller for MF0012/3","T1001",5.5,1
"T200601102";1/12/2006;"1' roller","T1012",50,1
"T200601103";1/13/2006;"A1 Papers White, A3 Paper Black","T1022",500,1
"T200601104";1/13/2006;"Folders","T1022",10,1

Now the problem is when i use bulk insert statement i put in the double quote (""") into the fields too. How can i solve this problem out?

Thanks in advance.You can use a format file with bcp/bulk insert to import data that contains field delimiters. You have to basically create dummy columns in the format file for the delimiter which you will ignore. See books online for more details on how to use format file with more columns than that of the table.