Geek asks and answers

Sql connection function

This is mainly a database connection issue, but this requires the use of the column-to-column mapping component.

I have several overview tables; for simplicity, let's say I have two, one since 2010 (called "d2010"), and the other since 2011 (called "d2011"). The rows correspond to the polls, and the columns (which we can consider named "c1", "c2", "c3", ... "cN") correspond to the answers to the questions. So, 'd2010' looks like

         id  c1  c2  c3  .. cX
survey 1
survey 2
survey 3
...
survey N

while 'd2011' looks like

         id  c1  c2  c3  .. cY
survey 1
survey 2
survey 3
...
survey N

- , 2010 2011 . , - SQL, ( ), , , :

         id  2010-response  2011-response
survey 1
survey 2
survey 3
...
survey N

, , , ,

select d2011.id, d2010.c1 as 'last year', d2011.c1 'this year' from d2010 join d2011 on d2010.id=d2011.id;

, , , ; ,

c1 of 2010 corresponds to c2 of 2011
c2 of 2010 corresponds to c1 of 2011
c3 of 2010 corresponds to c3 of 2011
c4 of 2010 corresponds to c7 of 2011
...

, , -

select d2010.id, d2010.c1 as 'last year', d2011.f(c1) 'this year' from d2010 join d2011 on d2010.id=d2011.id;

"d2011.f(c1)" , 2010 2011 . , , -

2010  2011
  c1    c2
  c2    c1
  c3    c3
  c4    c7

SQL , ? , , SQL ( ) SQLite ( )?

select d2011.id, f(d2010.c1) as 'last year', d2011.c1 'this year' from d2010 join d2011 on d2010.id=d2011.id;.id;

!

+3
2

, , . , , Dynamic SQL. :

declare @SQL nvarchar(1024)
set @SQL = 'select * from Table'
exec sp_executeSQL @SQL

, , "" :

    declare @SQL nvarchar(1024)
    declare @mappedColumnName nvarchar(1024)

    select @mappedColumnName = 2011 from mappedTable where 2010 = 'c1'

    set @SQL = 'select *
    from d2010 join d2011 on d2010.id=' + @SQL + ';.id';

    exec sp_executeSQL @SQL

, , , - Entity Framework .NET. LINQ.

"" , , . , Excel .

0

., :

select d2011.id, d2010.c2 as 'last year', d2011.c1 'this year'
from d2010 join d2011 on d2010.id = d2011.id

sql. SQL .

, , , , Excel, SQL Excel.

0

More articles:


All Articles