UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> Pivot Table, SQL Server 2008 R2    
 
   
mr.siro
post Aug 27 2019, 08:28 AM
Post#1



Posts: 185
Joined: 27-January 18



Hello guys, i have table as yellow area. How to query get result as blue area. Somebody help me please. Thank very much.

Attached File(s)
Attached File  Untitled.png ( 8.55K )Number of downloads: 2
 
Go to the top of the page
 
GroverParkGeorge
post Aug 27 2019, 08:53 AM
Post#2


UA Admin
Posts: 36,017
Joined: 20-June 02
From: Newcastle, WA


You can use a Crosstab query to produce this result, in part.

However, because you want to concatenate the results as well. I believe you'll need to start with a VBA function that does so. There are a couple of examples of that available, including one here in our Code Archives.
This post has been edited by GroverParkGeorge: Aug 27 2019, 08:54 AM

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
GroverParkGeorge
post Aug 27 2019, 08:55 AM
Post#3


UA Admin
Posts: 36,017
Joined: 20-June 02
From: Newcastle, WA


BTW: You've posted this in the SQL Server forum. Is this table in SQL Server?

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
mr.siro
post Aug 27 2019, 09:00 AM
Post#4



Posts: 185
Joined: 27-January 18



yes, it's SQL server table. Please help me. I used sqlserver and vb.net.
This post has been edited by mr.siro: Aug 27 2019, 09:01 AM
Go to the top of the page
 
GroverParkGeorge
post Aug 27 2019, 09:05 AM
Post#5


UA Admin
Posts: 36,017
Joined: 20-June 02
From: Newcastle, WA


The answer is the same, although you'll need to construct this as a Function in SQL Server to concatenate values first, and then use that in the SQL Server view that creates the pivot.




--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
mr.siro
post Aug 27 2019, 09:09 AM
Post#6



Posts: 185
Joined: 27-January 18



please tell me more detail to solved that. I try many but not work.
This post has been edited by mr.siro: Aug 27 2019, 09:10 AM
Go to the top of the page
 
GroverParkGeorge
post Aug 27 2019, 09:18 AM
Post#7


UA Admin
Posts: 36,017
Joined: 20-June 02
From: Newcastle, WA


It's not so simple, unfortunately.

GIve us some time to work it out.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
mr.siro
post Aug 27 2019, 09:26 AM
Post#8



Posts: 185
Joined: 27-January 18



yes, i think it not simple, so i must looking for help.
Go to the top of the page
 
MadPiet
post Aug 27 2019, 09:59 AM
Post#9



Posts: 3,354
Joined: 27-February 09



Can't remember what works on SQL 2008R2...

This works for me, but I'm on 2016.
CODE
USE tempdb;
GO

-- create tables
CREATE TABLE #Emp (
    TheDate DATE,
    Employee VARCHAR(10),
    Job VARCHAR(15));
GO

-- insert data
INSERT INTO #Emp (TheDate, Employee, Job)
VALUES ('01/01/2019','John','Security'),
('01/01/2019','Mary','Driver'),
('01/01/2019','Nick','Receptionist'),
('01/01/2019','Tom','Security');

-- actually answer the question
-- you need FOR XML PATH('')… to do this
SELECT DISTINCT TheDate
        , Job
        , EmployeeList = STUFF((SELECT ', ' + Employee
                 FROM #Emp e2
                 WHERE e2.TheDate = e1.TheDate
                 AND e2.Job = e1.Job
                 FOR XML PATH('')),1,1,'')
FROM #Emp e1
ORDER BY TheDate, Job;


Here's a short intro to STUFF(… FOR XML PATH...)
http://www.devx.com/tips/database-developm...1031122509.html
This post has been edited by MadPiet: Aug 27 2019, 10:38 AM
Go to the top of the page
 
GroverParkGeorge
post Aug 27 2019, 10:06 AM
Post#10


UA Admin
Posts: 36,017
Joined: 20-June 02
From: Newcastle, WA


Thanks. That's pretty efficient.

I was thinking about a function to concatenate Employees into strings:

CODE
USE [YourDatabaseNameGoesHere]
GO
/****** Object:  UserDefinedFunction [dbo].[GetWorkAssignment]    Script Date: 8/27/2019 8:05:25 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[GetWorkAssignment]

(
@WorkAssignmentGroup nvarchar(100)
)


RETURNS varchar(500)
WITH EXECUTE AS CALLER
AS
BEGIN
    DECLARE @WorkAssignment varchar(500),
            @WorkAssignmentPart varchar(100)

        DECLARE ContactInfoParts CURSOR
        FOR
        SELECT WA.[EmployeeName]   AS WorkAssignmentPart  
        FROM    [dbo].[tblWorkAssignment] WA
        WHERE WA.WorkAssigned =@WorkAssignmentGroup  
    
    OPEN ContactInfoParts

    FETCH NEXT FROM ContactInfoParts
    INTO @WorkAssignmentPart
    WHILE @@FETCH_STATUS = 0
        BEGIN
        SET @WorkAssignment= Concat(@WorkAssignment, @WorkAssignmentPart, ', ')
        FETCH NEXT FROM ContactInfoParts
        INTO @WorkAssignmentPart
        END
        SET @WorkAssignment= CASE WHEN @WorkAssignment IS NULL THEN 'NA' ELSE LEFT(@WorkAssignment, LEN(@WorkAssignment)-1) END
    CLOSE ContactInfoParts;
    DEALLOCATE ContactInfoParts;
    return @WorkAssignment
END


Not efficient because it's based on a cursor.

And a query with the pivot:

SQL
SELECT WorkDate, [Security] AS Security, [Driver] AS Driver, [Receptionist] AS Receptionist
FROM ( SELECT WorkDate, WorkAssigned , EmployeeName, [dbo].[GetWorkAssignment]([WorkAssigned]) as Work
FROM [WorkEffort].[dbo].[tblWorkAssignment]

) ps
PIVOT
( Max(Work)
FOR WorkAssigned IN ( [Security] , [Driver] , [Receptionist])
) AS pvt
GROUP BY WorkDate, [Security] , [Driver] , [Receptionist]



--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
PhilS
post Aug 27 2019, 10:07 AM
Post#11



Posts: 632
Joined: 26-May 15
From: The middle of Germany


A combination of STRING_AGG and PIVOT should produce the desired result.

--------------------
Go to the top of the page
 
MadPiet
post Aug 27 2019, 10:19 AM
Post#12



Posts: 3,354
Joined: 27-February 09



George,
that's what STUFF() with FOR XML does - it stuffs the values in the returned "table" into a delimited string. (Just say no to recordsets/cursors...)
Go to the top of the page
 
MadPiet
post Aug 27 2019, 10:23 AM
Post#13



Posts: 3,354
Joined: 27-February 09



STRING_AGG() isn't available in 2008R2. That's maybe a 2016 feature.
Go to the top of the page
 
PhilS
post Aug 27 2019, 10:31 AM
Post#14



Posts: 632
Joined: 26-May 15
From: The middle of Germany


QUOTE
STRING_AGG() isn't available in 2008R2. That's maybe a 2016 feature.

That's correct. I missed the SQL2008R2 requirement. - BTW: Extended support for SQL Server 2008 ended on July 9, 2019

I just upgraded a SQL Server to SQL 2017, so I couldn't resist to quickly test STRING_AGG. - Pretty nice. Here's a query delivering the intended results based on @MadPiet's #Emp-Table.

CODE
SELECT  TheDate, [Driver], [Receptionist], [Security]
    FROM (SELECT STRING_AGG (Employee , ' & ') AS emps,  TheDate, Job
            FROM #Emp
            GROUP BY TheDate, Job
         ) as sourceTable
PIVOT  ( MIN(emps)
FOR Job IN ([Driver], [Receptionist], [Security])  
) AS PivotTable;
GO

--------------------
Go to the top of the page
 
MadPiet
post Aug 27 2019, 10:37 AM
Post#15



Posts: 3,354
Joined: 27-February 09



I was going to add that 2008R2 is like 9 years old now... might be time for an upgrade.
Go to the top of the page
 
mr.siro
post Aug 27 2019, 08:10 PM
Post#16



Posts: 185
Joined: 27-January 18



hello guys, thank you guys very much, i solved it.
QUOTE
SELECT *
FROM
(SELECT DISTINCT job2.date, job2.work
, EmployeeList = STUFF((SELECT ', ' + job1.name
FROM dbo.job AS job1
WHERE job1.date = job2.date AND job1.work = job2.work
FOR XML PATH('')),1,1,'')
FROM dbo.job AS job2) AS tblSource
PIVOT (MIN(EmployeeList) FOR work IN ([Security] , [Driver] , [Receptionist])) AS job3


I have a question. I have table list of work. User can add more work in this table or delete work. When user add or delete work in this table, i must to repair query. How to custom funtion IN auto.
CODE
PIVOT (MIN(EmployeeList) FOR work IN ([Security] , [Driver] , [Receptionist]))
Go to the top of the page
 
MadPiet
post Aug 27 2019, 09:53 PM
Post#17



Posts: 3,354
Joined: 27-February 09



What I've heard is no - if you want a custom list in your IN () clause, you have to use dynamic SQL for the whole thing. Possible, but escaping all those single quotes looks like a serious hassle.
Go to the top of the page
 
mr.siro
post Aug 27 2019, 10:05 PM
Post#18



Posts: 185
Joined: 27-January 18



yes, i found some thing about dynamic query but i worry about security, like SQL injection. Have any ideals, guys ?
This post has been edited by mr.siro: Aug 27 2019, 10:05 PM
Go to the top of the page
 
MadPiet
post Aug 28 2019, 02:02 AM
Post#19



Posts: 3,354
Joined: 27-February 09



Here's the dynamic SQL to do it... Sheesh!


CODE
-- create tables
CREATE TABLE #Emp (
    TheDate DATE,
    Employee VARCHAR(10),
    Job VARCHAR(15));
GO
-- insert data
INSERT INTO #Emp (TheDate, Employee, Job)
VALUES ('01/01/2019','John','Security'),
('01/01/2019','Mary','Driver'),
('01/01/2019','Nick','Receptionist'),
('01/01/2019','Tom','Security');

-- insert some a new jobs
INSERT INTO #Emp (TheDate, Employee, Job)
VALUES ('01/01/2019','BWayne','Batman');

--insert some new dates for existing jobs
INSERT INTO #Emp (TheDate, Employee, Job)
VALUES ('02/01/2019','BWayne', 'Batman'),
('02/01/2019','Mary','Driver'),
('02/01/2019','Nick','Receptionist'),
('02/01/2019','Tom','Security');


Step 1: return a concatenated list of jobs (for the dynamic IN clause in the pivot.

CODE
/* the goal here is to return a concatenated list of jobs */
DECLARE @JobList VARCHAR(200);
SELECT DISTINCT
    @JobList = STUFF(
                    (SELECT DISTINCT ',' + '[' + job + ']'
                    FROM #Emp FOR XML PATH ('')), 1, 1, ''
                )
FROM (SELECT 1 AS ID, TheDate, Employee, Job FROM #Emp) x;


DECLARE @SQL VARCHAR(2000);
-- demonic SQL!!!
SET @SQL = 'SELECT * FROM
    (SELECT DISTINCT TheDate
            , Job
            , EmployeeList = STUFF((SELECT '', '' + Employee
                     FROM #Emp e2
                     WHERE e2.TheDate = e1.TheDate
                     AND e2.Job = e1.Job
                     FOR XML PATH('''')),1,1,'''')
    FROM #Emp e1) x
PIVOT (MIN(x.EmployeeList)
FOR Job IN ('+ @JobList + ')) AS Job3;'

EXEC (@SQL);



The injection question... there's really not a way that I can see that the user can inject anything, because none of the variables are exposed as parameters. Your stored procedure would start just before the DECLARE @JobList VARCHAR(200); line. Since the stored procedure doesn't accept values, how are you going to insert stuff into the @SQL variable? Even if you did add a couple of date parameters for a start date and an end date.

CODE
SELECT TheDate, Employee, Job
FROM #Emp
WHERE TheDate>=@StartDate AND TheDate <= @EndDate


how would you inject anything if you do this:
CODE
CREATE PROC GetWhatever
@StartDate DATE,
@EndDate DATE
AS
BEGIN
… <code from above>
END


I can see worrying about injection if you declare huge varchar input parameters and don't check anything, but I'm not doing that.







Go to the top of the page
 
mr.siro
post Aug 28 2019, 07:35 AM
Post#20



Posts: 185
Joined: 27-January 18



QUOTE
I can see worrying about injection if you declare huge varchar input parameters and don't check anything, but I'm not doing that.

What do you mean about this, can you tell me some example about: "declare huge varchar input parameters and don't check anything" ?

CODE
SET @SQL = 'SELECT * FROM
    (SELECT DISTINCT TheDate
            , Job
            , EmployeeList = STUFF((SELECT '', '' + Employee
                     FROM #Emp e2
                     WHERE e2.TheDate = e1.TheDate
                     AND e2.Job = e1.Job
                     FOR XML PATH('''')),1,1,'''')
    FROM #Emp e1
    WHERE e1.TheDate >= ''' + @StartDate + ''' AND e1.TheDate <= ''' + @EndDate + ''') x
PIVOT (MIN(x.EmployeeList)
FOR Job IN ('+ @JobList + ')) AS Job3;'

How about this, can i get SQL injection problem?

And, i change employee column to IDEmp in joblist table. I have table Employee with two column: IDEmp, NameEmp. I need to display NameEmp. So i change query:
CODE
SET @SQL = 'SELECT * FROM
    (SELECT DISTINCT TheDate
            , Job
            , EmployeeList = STUFF((SELECT '', '' + NameEmp
                     FROM #Emp AS e2 INNER JOIN tblEMP AS emp On emp.IDEmp=e2.IDEmp
                     WHERE e2.TheDate = e1.TheDate
                     AND e2.Job = e1.Job
                     FOR XML PATH('''')),1,1,'''')
    FROM #Emp e1
    WHERE e1.TheDate >= ''' + @StartDate + ''' AND e1.TheDate <= ''' + @EndDate + ''' ) x
PIVOT (MIN(x.EmployeeList)
FOR Job IN ('+ @JobList + ')) AS Job3;'

Query work fine, but is it optimal ?
Employee have 3k record, and joblist table have 200k record and it increase very fast.
This post has been edited by mr.siro: Aug 28 2019, 07:49 AM
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search


RSSSearch   Top   Lo-Fi    12th November 2019 - 03:59 AM