Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 VIEW школьного журнала из имеющихся таблиц  [new]
casu
Member

Откуда:
Сообщений: 133
Добрый день!
Что в наличии:

Таблица "Ученики" STUDENT
ID  NAME
1.   Иванов А.А.
2.   Петров Б.Б.
3.   Сидоров В.В.


Таблица "Предметы" SUBJECT
ID  NAME
1.  Математика
2.  История
3.  География


Таблица "Оценки" EVAL
ID  ID_STUDENT  ID_SUBJECT  DATE_EVAL  EVAL
1.  1           1           12.12.2014  5 
2.  1           2           13.12.2014  4 
3.  1           3           14.12.2014  5 
4.  2           1           12.12.2014  3 
5.  2           2           13.12.2014  4 
6.  2           1           13.12.2014  3 
7.  3           1           14.12.2014  3 
8.  3           3           14.12.2014  4 
9.  3           1           15.12.2014  5 
10. 1           1           16.12.2014  5 



Вопрос: Помогите построить VIEW следующего вида:
выбрать предмет (МАТЕМАТИКА ID_SUBJECT = 1) и получить:
                     12.12.2014   13.12.2014   14.12.2014   15.12.2014   16.12.2014 
1.   Иванов А.А.     5                                                   5
2.   Петров Б.Б.     3            3
3.   Сидоров В.В.                              3            5
17 янв 15, 10:48    [17131623]     Ответить | Цитировать Сообщить модератору
 Re: VIEW школьного журнала из имеющихся таблиц  [new]
Добрый Э - Эх
Guest
casu,

PIVOT или его CASE+GROUP BY аналог на древних версиях.
17 янв 15, 12:01    [17131754]     Ответить | Цитировать Сообщить модератору
 Re: VIEW школьного журнала из имеющихся таблиц  [new]
casu
Member

Откуда:
Сообщений: 133
Конечно же предпочтительнее PIVOT!
Но spreading element в PIVOT (другими словами в те значения строк, которые должны развернуться в столбцы), ... согласно примеру: "даты занятий и оценок" (dbo.EVAL.D_EVAL) не статичны, а меняются в зависимости от запроса.
Они у разных предметов - разные и не идут подряд с 1 по 31 число месяца.
Выводить данные dbo.EVAL.D_EVAL в столбцы нужно именно с те, которые соответствуют предмету dbo.EVAL.SUBJECT_ID?


WITH PivotData AS
(
  SELECT
    STUDENT_ID
   ,STUDENT.NAME
   ,D_EVAL
   ,EVAL
  FROM EVAL 
   LEFT JOIN STUDENT ON (EVAL.STUDENT_ID=STUDENT.ID)
   WHERE SUBJECT_ID = 8 --это значение выбранного предмета
)
SELECT 
 STUDENT_ID
,NAME AS STUDENT_NAME
, [16.01.2015], [17.01.2015], [18.01.2015], [19.01.2015], [20.01.2015]--даты которые соответствуют предмету
FROM PivotData
  PIVOT(AVG(eval) FOR D_EVAL IN ([16.01.2015], [17.01.2015], [18.01.2015], [19.01.2015], [20.01.2015]) ) AS P;


Подскажите идею, как данные: [16.01.2015], [17.01.2015], [18.01.2015], [19.01.2015], [20.01.2015] динамически выводить их в запрос PIVOT?
20 янв 15, 13:56    [17142822]     Ответить | Цитировать Сообщить модератору
 Re: VIEW школьного журнала из имеющихся таблиц  [new]
Добрый Э - Эх
Guest
casu,

сам себе и ответил же:
casu
динамически
.
вот собственно про динамический SQL и читай...
20 янв 15, 14:00    [17142853]     Ответить | Цитировать Сообщить модератору
 Re: VIEW школьного журнала из имеющихся таблиц  [new]
casu
Member

Откуда:
Сообщений: 133
В смысле:
1) результаты запроса по датам генерировать в одну строку
2) там же форматировать запятыми и скобками,
2) вставлять в общую строковую переменную запроса (например
@sql
)

И запускать типа:
exec (@sql);

?
20 янв 15, 14:08    [17142915]     Ответить | Цитировать Сообщить модератору
 Re: VIEW школьного журнала из имеющихся таблиц  [new]
LexusR
Member

Откуда: Novosibirsk
Сообщений: 1887
нет
в переменную @sql сгенерировать pivot динамически c нужными колонками и запустить
20 янв 15, 14:45    [17143135]     Ответить | Цитировать Сообщить модератору
 Re: VIEW школьного журнала из имеющихся таблиц  [new]
LexusR
Member

Откуда: Novosibirsk
Сообщений: 1887
вот что-то по аналогии с этим
if OBJECT_ID('tempdb..#table') is not null drop table #table
select 'AAA' as Mnf, 'A1' as Name, 1 as IDA into #table 
union select 'BBB' ,'B1', 1
union select 'CCC', 'C1', 1
union select 'DDD', 'D1', 2 ;

declare @cols nvarchar(max)=stuff(
(select ',' + quotename(NCol) as [text()]
from (select distinct row_number()OVER(partition by IDA order by Mnf+'.'+Name) as NCol 
from #table) as d order by NCol 
for xml path(''), type).value('.[1]', 'varchar(max)'),1, 1, '');

declare @sql nvarchar(max)='with pvt as (
select IDA,Mnf+''.''+Name as Name
,row_number()OVER(partition by IDA order by Mnf+''.''+Name)  as NCol
from #table)
select * from pvt
pivot(max(Name) for NCol in ('+@cols+')) as p';
exec sp_executesql @sql;
20 янв 15, 14:54    [17143203]     Ответить | Цитировать Сообщить модератору
 Re: VIEW школьного журнала из имеющихся таблиц  [new]
casu
Member

Откуда:
Сообщений: 133
Сделал следующим образом: все работает.
LexusR, спасибо за идею.

Есть ли замечания к коду?

USE tempdb;
GO

if OBJECT_ID('tempdb..#tbSTUDENT') IS NOT NULL DROP TABLE #tbSTUDENT
CREATE TABLE #tbSTUDENT ([ID] integer IDENTITY(1,1), [NAME] nvarchar(50)) 
GO

if OBJECT_ID('tempdb..#tbSUBJECT') IS NOT NULL DROP TABLE #tbSUBJECT 
CREATE TABLE #tbSUBJECT ([ID] integer IDENTITY(1,1), [NAME] nvarchar(50)) 
GO

if OBJECT_ID('tempdb..#tbEVAL') IS NOT NULL DROP TABLE #tbEVAL
CREATE TABLE #tbEVAL ([ID] integer IDENTITY(1,1), [STUDENT_ID] integer, [SUBJECT_ID] integer, D_EVAL datetime2(0), EVAL integer)
GO

if OBJECT_ID('tempdb..#spVIEW_SUBJECT') IS NOT NULL DROP PROCEDURE #spVIEW_SUBJECT
GO
--Процедура, которой передается параметр "Предмет" (по которому интересует статистика оценок учеников)
CREATE PROCEDURE #spVIEW_SUBJECT @SUBJECT int
AS
BEGIN 
SET NOCOUNT ON;
DECLARE @sql nvarchar(1000);
SET @sql = '
WITH PivotData AS
(SELECT STUDENT_ID, S.NAME, D_EVAL, EVAL FROM #tbEVAL AS E 
	LEFT JOIN #tbSTUDENT AS S ON (E.STUDENT_ID=S.ID)
	WHERE SUBJECT_ID = '+CAST(@SUBJECT as varchar)+')
SELECT STUDENT_ID, NAME AS STUDENT_NAME, '+(SELECT REPLACE(STUFF((SELECT DISTINCT ',' + QUOTENAME(CAST([D_EVAL] as nvarchar))  as 'data()' from #tbEVAL for xml path('')),1,1,''),' 00:00:00',''))+'
FROM PivotData
  PIVOT(MAX(EVAL) FOR D_EVAL IN ('+(SELECT REPLACE(STUFF((SELECT DISTINCT ',' + QUOTENAME(CAST([D_EVAL] as nvarchar))  as 'data()' from #tbEVAL for xml path('')),1,1,''),' 00:00:00',''))+') ) AS P;
	';
exec(@sql);
END
GO
--Таблица учеников
INSERT INTO #tbSTUDENT (NAME) VALUES ('Иванов Иван'), ('Петров Петя'), ('Сидоров Сидор'), ('Михайлов Миша')
--Таблица предметов
INSERT INTO #tbSUBJECT (NAME) VALUES ('Математика'), ('История'), ('География'), ('Информатика')
--Таблица оценок (Поля: ученик, предмет, дата оценки, оценка)
INSERT INTO #tbEVAL (STUDENT_ID, SUBJECT_ID, D_EVAL, EVAL) VALUES 
 (1,1,'10.01.2015',5),(1,2,'10.01.2015',5),(1,3,'10.01.2015',5),(1,4,'10.01.2015',5)
,(2,1,'10.01.2015',5),(2,1,'12.01.2015',5),(2,3,'10.01.2015',5),(2,4,'13.01.2015',5)
,(3,1,'12.01.2015',5),(3,2,'10.01.2015',5),(3,2,'14.01.2015',5),(3,3,'10.01.2015',5)
,(4,2,'14.01.2015',5),(4,3,'15.01.2015',5),(4,4,'10.01.2015',5),(4,4,'13.01.2015',5)

--
exec #spVIEW_SUBJECT 1
--
26 янв 15, 20:19    [17173408]     Ответить | Цитировать Сообщить модератору
 Re: VIEW школьного журнала из имеющихся таблиц  [new]
Glory
Member

Откуда:
Сообщений: 104751
casu
Есть ли замечания к коду?

Что будет, если размер текста запроса превысит nvarchar(1000) ?
26 янв 15, 20:21    [17173415]     Ответить | Цитировать Сообщить модератору
 Re: VIEW школьного журнала из имеющихся таблиц  [new]
casu
Member

Откуда:
Сообщений: 133
nvarchar(max)
26 янв 15, 20:28    [17173440]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить