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

Откуда:
Сообщений: 24
Доброго времени суток! На форумах есть темы с подобным вопросом, где нужно значения из столбца поместить в одну строку в соответствии с каким-то условием. Но мне нужно сделать это через PIVOT/UNPIVOT.

для каждого empid поместить все его company в одну строку

Например:
как есть

К сообщению приложен файл. Размер - 16Kb
6 фев 19, 21:04    [21803085]     Ответить | Цитировать Сообщить модератору
 Re: PIVOT/UNPIVOT несколько значений из столбца в одну строку  [new]
Kotovach
Member

Откуда:
Сообщений: 24
как должно быть

К сообщению приложен файл. Размер - 16Kb
6 фев 19, 21:05    [21803086]     Ответить | Цитировать Сообщить модератору
 Re: PIVOT/UNPIVOT несколько значений из столбца в одну строку  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 29293
Kotovach
Но мне нужно сделать это через PIVOT/UNPIVOT.
В общем случае это невозможно.
6 фев 19, 21:20    [21803093]     Ответить | Цитировать Сообщить модератору
 Re: PIVOT/UNPIVOT несколько значений из столбца в одну строку  [new]
invm
Member

Откуда: Москва
Сообщений: 8802
Kotovach
Но мне нужно сделать это через PIVOT/UNPIVOT.
Только если напишите соответсвующую агрегатную функцию (https://docs.microsoft.com/ru-ru/sql/relational-databases/user-defined-functions/create-user-defined-aggregates?view=sql-server-2017) с IsInvariantToNulls = true
Со штатной string_agg не взлетит.
6 фев 19, 21:48    [21803101]     Ответить | Цитировать Сообщить модератору
 Re: PIVOT/UNPIVOT несколько значений из столбца в одну строку  [new]
Kotovach
Member

Откуда:
Сообщений: 24
invm, спасибо за наводку, но такой функционал доступен с 2016 server. В моем случае лицензия на 2012.
6 фев 19, 21:55    [21803104]     Ответить | Цитировать Сообщить модератору
 Re: PIVOT/UNPIVOT несколько значений из столбца в одну строку  [new]
Kotovach
Member

Откуда:
Сообщений: 24
alexeyvg, все больше думаю, что Вы правы)
написала решение общее
USE tempdb
IF OBJECT_ID('dbo.CONsum', 'P') IS NOT NULL
DROP PROC dbo.CONsum;
GO

CREATE FUNCTION dbo.CONsum(@id INTEGER)
RETURNS NVARCHAR(1000)
AS
BEGIN
DECLARE @Result NVARCHAR(4000)
SET @Result = ''
SELECT @Result = @Result + companyname + ', ' FROM (SELECT companyname, empid
FROM TestSQL.Sales.Orders AS SO
,TestSQL.Sales.Customers AS SC
WHERE SO.custid=SC.custid) AS qqq
WHERE empid = @id;
RETURN '"' + RTRIM(@Result) + '"'
END
GO

но на PIVOT как указано в задании переделать не могу...
6 фев 19, 22:59    [21803121]     Ответить | Цитировать Сообщить модератору
 Re: PIVOT/UNPIVOT несколько значений из столбца в одну строку  [new]
Kotovach
Member

Откуда:
Сообщений: 24
Весь кода
USE tempdb
IF OBJECT_ID('dbo.CONsum', 'P') IS NOT NULL
  DROP PROC dbo.CONsum;
GO

CREATE FUNCTION dbo.CONsum(@id INTEGER)
RETURNS NVARCHAR(1000)
AS
BEGIN
DECLARE @Result NVARCHAR(4000)
SET @Result = ''
SELECT @Result = @Result + companyname + ', ' 
                      FROM (SELECT companyname, empid
FROM TestSQL.Sales.Orders AS SO
															 ,TestSQL.Sales.Customers AS SC
													WHERE SO.custid=SC.custid) AS rez1
												WHERE empid = @id;
RETURN '"' + RTRIM(@Result) + '"'
END
GO

SELECT DISTINCT empid, dbo.CONsum(empid) FROM TestSQL.Sales.Orders
GO
6 фев 19, 23:01    [21803122]     Ответить | Цитировать Сообщить модератору
 Re: PIVOT/UNPIVOT несколько значений из столбца в одну строку  [new]
Kotovach
Member

Откуда:
Сообщений: 24
не обращайте на мои ранние сообщения внимание)

USE tempdb
IF OBJECT_ID('dbo.CONsum', 'F') IS NOT NULL
  DROP PROC dbo.CONsum;
GO

CREATE FUNCTION dbo.CONs(@id INTEGER)
RETURNS NVARCHAR(1000)
AS
BEGIN
DECLARE @Result NVARCHAR(4000)
SET @Result = ''
SELECT @Result = @Result + companyname + ', '
                FROM (SELECT distinct companyname, SO.empid
				FROM TestSQL.Sales.Orders AS SO
			                 ,TestSQL.Sales.Customers AS SC
						WHERE SO.custid=SC.custid
			   ) AS qqq
		WHERE empid = @id;
RETURN '"' + RTRIM(@Result) + '"'
END
GO

SELECT DISTINCT empid, dbo.CONs(empid) FROM TestSQL.Sales.Orders
GO
6 фев 19, 23:24    [21803130]     Ответить | Цитировать Сообщить модератору
 Re: PIVOT/UNPIVOT несколько значений из столбца в одну строку  [new]
invm
Member

Откуда: Москва
Сообщений: 8802
Kotovach,

Покажите оригинальный текст задания.
7 фев 19, 09:39    [21803227]     Ответить | Цитировать Сообщить модератору
 Re: PIVOT/UNPIVOT несколько значений из столбца в одну строку  [new]
Kotovach
Member

Откуда:
Сообщений: 24
invm,

И задание необходимо выполнять в соответсвии с указаной темой Pivot/Unpivot/DynamicSQL

Show the list of distinct customers who placed orders with each employee. Create two queries one for SQL Server 2017 version and another for older version of SQL Server

далее следуют изображения, раннее уже прикрепленные

Я тоже пришла к выводу, что делать через Pivot это лишнее, обсудили с наставником, пришли к согласию о предыдущем решении=)

Всем спасибо!)
7 фев 19, 11:12    [21803324]     Ответить | Цитировать Сообщить модератору
 Re: PIVOT/UNPIVOT несколько значений из столбца в одну строку  [new]
invm
Member

Откуда: Москва
Сообщений: 8802
Kotovach
обсудили с наставником, пришли к согласию о предыдущем решении=)
А ничего, что это решение к задаче "Show the list of distinct customers who placed orders with each employee" вообще никак не относится?
7 фев 19, 11:42    [21803364]     Ответить | Цитировать Сообщить модератору
 Re: PIVOT/UNPIVOT несколько значений из столбца в одну строку  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 3969
Kotovach,

https://docs.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-2017

SELECT town, 
    STRING_AGG (email, ';') WITHIN GROUP (ORDER BY email ASC) AS emails 
FROM dbo.Employee 
GROUP BY town; 


2017 +
7 фев 19, 12:44    [21803412]     Ответить | Цитировать Сообщить модератору
 Re: PIVOT/UNPIVOT несколько значений из столбца в одну строку  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 29293
Kotovach
Я тоже пришла к выводу, что делать через Pivot это лишнее, обсудили с наставником, пришли к согласию о предыдущем решении=)
А, а сначала препод сказал про обязательное использование pivot?
Без него то есть разные способы, например, тут в ФАКе есть несколько ваниантов, допустим, через XML
https://www.sql.ru/faq/faq_topic.aspx?fid=130
use tempdb
go
drop table test
go
create table dbo.TEST ([Тип] INTEGER, [Имя] NVARCHAR(100), [Количество] INTEGER)
go
INSERT dbo.TEST VALUES(1, N'Молоко', 5)
INSERT dbo.TEST VALUES(1, N'Сметана', 6)
INSERT dbo.TEST VALUES(2, N'Гвозди', 44)
INSERT dbo.TEST VALUES(3, N'Машины', 1)

select [Тип] ,( select [Имя]+': '+cast([Количество] as nvarchar)+ ',' as 'data()' from test t2 where t1.[Тип]=t2.[Тип] for xml path('') )
from dbo.TEST t1
group by [Тип]
7 фев 19, 14:57    [21803568]     Ответить | Цитировать Сообщить модератору
 Re: PIVOT/UNPIVOT несколько значений из столбца в одну строку  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 3969
alexeyvg,

А вы в курсе, что это некорректно работает при наличии спецсимволов?

use tempdb
go
drop table test
go
create table dbo.TEST ([Тип] INTEGER, [Имя] NVARCHAR(100), [Количество] INTEGER)
go
INSERT dbo.TEST VALUES(1, N'Моло<ко', 5)
INSERT dbo.TEST VALUES(1, N'Сметана', 6)
INSERT dbo.TEST VALUES(2, N'Гвоз>ди', 44)
INSERT dbo.TEST VALUES(2, N'М<ашины', 1)

select [Тип] ,( select [Имя]+': '+cast([Количество] as nvarchar)+ ',' as 'data()' from test t2 where t1.[Тип]=t2.[Тип] for xml path('') )
from dbo.TEST t1
group by [Тип]



1	Моло&lt;ко: 5, Сметана: 6,
2	Гвоз&gt;ди: 44, М&lt;ашины: 1,
7 фев 19, 16:22    [21803668]     Ответить | Цитировать Сообщить модератору
 Re: PIVOT/UNPIVOT несколько значений из столбца в одну строку  [new]
iap
Member

Откуда: Москва
Сообщений: 46862
SELECT [Тип], STUFF((SELECT N', '+[Имя]+N': '+CAST([Количество] AS NVARCHAR) FROM dbo.TEST t2 WHERE t1.[Тип]=t2.[Тип] FOR XML PATH(''),TYPE).value('.','NVARCHAR(100)'),1,2,N'')
FROM dbo.TEST t1
GROUP BY [Тип];
7 фев 19, 16:42    [21803692]     Ответить | Цитировать Сообщить модератору
 Re: PIVOT/UNPIVOT несколько значений из столбца в одну строку  [new]
invm
Member

Откуда: Москва
Сообщений: 8802
И все упорно продолжают решать исходную задачу агрегированием строк Картинка с другого сайта.
7 фев 19, 16:57    [21803699]     Ответить | Цитировать Сообщить модератору
 Re: PIVOT/UNPIVOT несколько значений из столбца в одну строку  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 29293
invm
И все упорно продолжают решать исходную задачу агрегированием строк Картинка с другого сайта.
Мда, действительно....
Как связанл это:
Kotovach
для каждого empid поместить все его company в одну строку

С этим?
Kotovach
Show the list of distinct customers who placed orders with each employee.
7 фев 19, 18:57    [21803802]     Ответить | Цитировать Сообщить модератору
 Re: PIVOT/UNPIVOT несколько значений из столбца в одну строку  [new]
Kotovach
Member

Откуда:
Сообщений: 24
alexeyvg,

потому что не видно всех промежуточных таблиц. Задача не тот исходник, что просили, а интерпритированное условие

всем спасибо
8 фев 19, 14:57    [21804426]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить