March 2, 2012 at 3:17 am
Hi,
How to convert rows into Column using pivot query.
I have two tables
Table: PS_EMPLOYEES
Emplid Deptid
20001 43200
20002 43200
20003 43300
Another table: PS_HW_DEPT_ROLES
Setid deptid HW_ROLE HW_ROLE_ID
CHSID 43200 HRA 56783
CHSID 43200 HRA 98989
CHSID 43200 HRA 66768
CHSID 43200 PRI 44545
CHSID 43300 PRI 89088
CHSID 43300 HRA 77978
CHSID 43300 HRA 76767
Like Wise It consists of values
I need output like
Emplid Deptid PRI HRA HRA HRA
20001 43200 44545 56783 98989 66768
20002 43200 44545 56783 98989 66768
20003 43300 89088 77978 76767
March 2, 2012 at 4:30 am
Does it need to be dynamic? Or do you always know that there are no more than 3 HRA ?
I'd use CROSS-TABS instead of PIVOT, as I've found that PIVOT tends to be slower (feel free to test yourself). Below is a static version for if you know that there are never more than 3 HRA.
--Create sample data PS_EMPLOYEES
SELECT Emplid, Deptid
INTO PS_EMPLOYEES
FROM (VALUES(20001, 43200),(20002, 43200),(20003, 43300))a(Emplid, Deptid)
--Create sample data PS_HW_DEPT_ROLES
SELECT Setid, deptid, HW_ROLE, HW_ROLE_ID
INTO PS_HW_DEPT_ROLES
FROM (VALUES('CHSID', 43200, 'HRA', 56783),('CHSID', 43200, 'HRA', 98989),
('CHSID', 43200, 'HRA', 66768),('CHSID', 43200, 'PRI', 44545),
('CHSID', 43300, 'PRI', 89088),('CHSID', 43300, 'HRA', 77978),
('CHSID', 43300, 'HRA', 76767))a(Setid, deptid, HW_ROLE, HW_ROLE_ID)
--Static version of query (works if you know that you only have at most 3 HRA)
SELECT Emplid, Deptid,
MAX(CASE WHEN HW_ROLE = 'PRI' THEN HW_ROLE_ID ELSE NULL END) AS [PRI],
MAX(CASE WHEN HW_ROLE = 'HRA' AND rn=1 THEN HW_ROLE_ID ELSE NULL END) AS [HRA],
MAX(CASE WHEN HW_ROLE = 'HRA' AND rn=2 THEN HW_ROLE_ID ELSE NULL END) AS [HRA],
MAX(CASE WHEN HW_ROLE = 'HRA' AND rn=3 THEN HW_ROLE_ID ELSE NULL END) AS [HRA]
FROM (SELECT emp.Emplid, emp.Deptid,
rol.HW_ROLE, rol.HW_ROLE_ID,
ROW_NUMBER() OVER (PARTITION BY emp.Emplid, emp.Deptid, rol.HW_ROLE ORDER BY (SELECT NULL)) AS rn
FROM PS_EMPLOYEES emp
INNER JOIN PS_HW_DEPT_ROLES rol ON emp.Deptid = rol.deptid) innerQuery
GROUP BY Emplid, Deptid
Returns: -
Emplid Deptid PRI HRA HRA HRA
----------- ----------- ----------- ----------- ----------- -----------
20001 43200 44545 56783 98989 66768
20002 43200 44545 56783 98989 66768
20003 43300 89088 77978 76767 NULL
March 4, 2012 at 8:54 pm
Hi Cadavre,
Thank So you much.. 🙂
March 4, 2012 at 9:01 pm
Hi,
Once Again Thank you So much
I need dynamic. Sometimes it will be more than 3 HRA.
March 5, 2012 at 3:02 am
chandarcst (3/4/2012)
Hi,Once Again Thank you So much
I need dynamic. Sometimes it will be more than 3 HRA.
Something like this: -
DECLARE @SQL AS NVARCHAR(MAX);
WITH t1(N) AS (SELECT 1 UNION ALL SELECT 1),
t2(N) AS (SELECT 1 FROM t1 x, t1 y),
t3(N) AS (SELECT 1 FROM t2 x, t2 y),
Tally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM t3 x, t3 y),
maxRn(N) AS (SELECT MAX(rn)
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY emp.Emplid, emp.Deptid, rol.HW_ROLE ORDER BY (SELECT NULL)) AS rn
FROM PS_EMPLOYEES emp
INNER JOIN PS_HW_DEPT_ROLES rol ON emp.Deptid = rol.deptid) a)
SELECT @SQL = COALESCE(@SQL,'') + sqlData
FROM (SELECT 'SELECT Emplid, Deptid', 1
UNION ALL
SELECT ', MAX(CASE WHEN HW_ROLE = '+CHAR(39)+'HRA'+CHAR(39)+' AND rn='+CAST(N AS VARCHAR(5))+' THEN HW_ROLE_ID ELSE NULL END) AS [HRA]', N+1
FROM Tally
WHERE N <= (SELECT N FROM maxRn)
UNION ALL
SELECT 'FROM (SELECT emp.Emplid, emp.Deptid,' +
'rol.HW_ROLE, rol.HW_ROLE_ID,' +
'ROW_NUMBER() OVER (PARTITION BY emp.Emplid, emp.Deptid, rol.HW_ROLE ORDER BY (SELECT NULL)) AS rn ' +
'FROM PS_EMPLOYEES emp ' +
'INNER JOIN PS_HW_DEPT_ROLES rol ON emp.Deptid = rol.deptid) innerQuery ' +
'GROUP BY Emplid, Deptid', (SELECT N+2 FROM maxRn)) innerQuery(sqlData,ordering)
ORDER BY ordering;
EXECUTE sp_executesql @SQL;
March 5, 2012 at 3:06 am
Hi,
Thank you So much...
I want to learn sql query So please send the standard tutorial links
March 5, 2012 at 4:10 am
chandarcst (3/5/2012)
Hi,Thank you So much...
I want to learn sql query So please send the standard tutorial links
Don't really understand what you're asking.
Read more about the cross-tabs technique in Jeff Moden's articles.
Part 1 --> http://www.sqlservercentral.com/articles/T-SQL/63681/%5B/url%5D
Part 2 --> http://www.sqlservercentral.com/articles/Crosstab/65048/%5B/url%5D
March 5, 2012 at 4:15 am
Hi
I asked you Send the standard sql tutorial links suppose if you have.
March 5, 2012 at 4:20 am
chandarcst (3/5/2012)
HiI asked you Send the standard sql tutorial links suppose if you have.
I have no idea what you want me to send you. What "standard sql tutorial" ?
March 5, 2012 at 5:00 am
Cadavre (3/5/2012)
chandarcst (3/5/2012)
HiI asked you Send the standard sql tutorial links suppose if you have.
I have no idea what you want me to send you. What "standard sql tutorial" ?
:hehe:
March 5, 2012 at 5:09 am
chandarcst (3/5/2012)
HiI asked you Send the standard sql tutorial links suppose if you have.
http://msdn.microsoft.com/en-us/library/ms203721(v=sql.90).aspx
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
March 5, 2012 at 7:31 am
chandarcst (3/5/2012)
HiI asked you Send the standard sql tutorial links suppose if you have.
There's no real "standard tutorial" links on the subject by Microsoft. The information is in Books Online and it's becoming difficult to find the information on Cross-Tabs that MS used to carry in BOL because they're pushing PIVOT.
As Cadavre suggested, PIVOT is actually slower than the "standard" Cross-Tab methods and that's explained in the first link he provided. The second link he provided tells you how to create dynamic SQL for dynamic Cross-Tabs. You should look at them because they both written in a "tutorial" fashion. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
March 5, 2012 at 9:50 pm
Hi,
I want to learn sql and tell me the way
March 6, 2012 at 12:11 am
Thank so much for all of your suggestions...
March 6, 2012 at 1:37 am
chandarcst (3/5/2012)
Hi,I want to learn sql and tell me the way
Well, personally I am learning T-SQL by reading countless books and sticking around here answering questions posed by others. Often, someone else will come a long with an answer that is far superior so I dig away at it until I understand how it works and all of the limitations then it becomes a new "thing" that I've learnt. I try and tick off one new thing learnt every day, so after 2 year of working with SQL (first job was in January 2010), I now finally know about 0.1% of how T-SQL works 😀
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply