HOW TO CREATE PIVOT QUERY CREATE

Question

0.00/5 (No votes)

See more:

SQL-Server-2012

, +

HTML

<pre lang="xml">
</pre>

hi,,

I GOT RESULT FROM QUERY :

TITAL   PCS RGH_CTS    VALUE
SEND    10  49.8550    198742.94400
BID     15  76.7070      327461.76300
FINAL   19  88.5630    337954.15600

I WANT LIKE THIS OUT PUT  : HOW TO SOLVE THIS ,,,

| TITAL     |    SEND      |    BID       |   FINAL      |
| PCS       |     10       |    15        |     19       |
| RGH_CTS   |   49.8550    |  76.7070     |   88.5630    |
| VALUE     | 198742.94400 | 327461.76300 | 337954.15600 |


ANY BUDDY CAN SOLVE THIS MY SIMPLE QUERY  ?.
THANKS IN ADVANCE :</pre>

Posted 25-Dec-14 22:47pm

RKparmar

Add a Solution

Comments

King Fisher 26-Dec-14 4:48am

Do not use Capital Letters.
What's your Query ?

RKparmar 28-Dec-14 0:22am

SELECT TITAL,PCS,RGH_CTS,VALUE
FROM (
SELECT COUNT(VPACKET_NO) PCS, SUM(CTS) RGH_CTS , SUM(TTL_NET_VALUE) VALUE , 'SEND' TITAL
FROM (
SELECT SEQ_NO,VPACKET_NO,CTS,MAX(TOTAL_NET_VALUE) TTL_NET_VALUE FROM (
SELECT ID.SEQ_NO, ID.VPACKET_NO,ID.CTS,
SUM((DBO.DISC_RATE(ISNULL(psd.RATE, ISNULL(tp.RATE,tp.RATE)), ISNULL(psd.DISC_PER, ISNULL(tp.DISC_PER,0))) * ROUND(ISNULL(psd.EXP_CTS, tp.EXP_CTS),3,1))) OVER (PARTITION BY tp.TRANS_ID, tp.PLAN_SEQ) TOTAL_NET_VALUE
FROM INV_DET ID
INNER JOIN TRANS_PLAN TP ON TP.INV_DET_SEQ = ID.SEQ_NO
LEFT JOIN PLAN_SEND_DET PSD ON PSD.SEQ_NO = TP.SEQ_NO
INNER JOIN PLAN_SEND_MAS PSM ON PSM.SEQ_NO = PSD.PLAN_SEND_SEQ
WHERE CAST(PSM.TRANS_DATE AS DATE) = CAST(GETDATE()-3 AS DATE) and
PSD.CANCEL_DATE IS NULL
)X
GROUP BY SEQ_NO,VPACKET_NO,CTS
)SND
UNION ALL
SELECT COUNT(VPACKET_NO) PCS , SUM(CTS) CTS , SUM(TTL_NET_VALUE) VALUE , 'BID' TITAL
FROM (
SELECT SEQ_NO,VPACKET_NO,CTS,MAX(TOTAL_NET_VALUE) TTL_NET_VALUE FROM (
SELECT ID.SEQ_NO, ID.VPACKET_NO,ID.CTS,
SUM((DBO.DISC_RATE(ISNULL(psd.RATE,TP.RATE), ISNULL(pb.DISC_PER,ISNULL(psd.DISC_PER,TP.DISC_PER))) * ROUND(ISNULL(psd.EXP_CTS, tp.EXP_CTS),3,1))) OVER (PARTITION BY tp.TRANS_ID, tp.PLAN_SEQ) TOTAL_NET_VALUE
FROM INV_DET ID
INNER JOIN TRANS_PLAN TP ON TP.INV_DET_SEQ = ID.SEQ_NO
INNER JOIN PLAN_SEND_DET PSD ON PSD.SEQ_NO = TP.SEQ_NO AND TP.IS_REJECT = 0
INNER JOIN PLAN_BID_DET PBD ON TP.SEQ_NO = PBD.TRANS_PLAN_SEQ
INNER JOIN PLAN_BID_MAS PBM ON PBD.PLAN_BID_SEQ = PBM.SEQ_NO
LEFT JOIN ( SELECT pbdX.*, pbmX.TRANS_DATE BID_DATE
FROM PLAN_BID_DET pbdX
INNER JOIN PLAN_BID_MAS pbmX on pbdX.PLAN_BID_SEQ = pbmX.SEQ_NO
INNER JOIN PLAN_SEND_DET psdX on pbdX.TRANS_PLAN_SEQ = psdX.SEQ_NO AND psdX.CANCEL_DATE IS NULL
WHERE pbmX.BIDDER_SEQ = ISNULL(23, pbmX.BIDDER_SEQ)
) pb ON pb.TRANS_PLAN_SEQ = psd.SEQ_NO
LEFT JOIN NAME_MAS NM ON NM.SEQ_NO = TP.EMP_SEQ
WHERE CAST(pbm.TRANS_DATE AS DATE) = CAST(GETDATE()-3 AS DATE)
AND PBD.CANCEL_DATE IS NULL AND PSD.BID_DET_SEQ IS NOT NULL
AND tp.TRANS_TYPE_SEQ IN (1162)
AND NM.CODE_NO <> 'TRY'
AND TP.IS_REJECT = 0
)X
GROUP BY SEQ_NO,VPACKET_NO,CTS
)BID
UNION ALL
SELECT COUNT(VPACKET_NO) PCS , SUM(CTS) CTS , SUM(TTL_NET_VALUE) VALUE , 'FINAL' TITAL
FROM (
SELECT SEQ_NO,VPACKET_NO,CTS,MAX(TOTAL_NET_VALUE) TTL_NET_VALUE FROM (
SELECT ID.SEQ_NO, ID.VPACKET_NO,ID.CTS,
SUM((DBO.DISC_RATE(ISNULL(psd.RATE,TP.RATE), ISNULL(pb.DISC_PER,ISNULL(psd.DISC_PER,TP.DISC_PER))) * ROUND(ISNULL(psd.EXP_CTS, tp.EXP_CTS),3,1))) OVER (PARTITION BY tp.TRANS_ID, tp.PLAN_SEQ) TOTAL_NET_VALUE
FROM INV_DET ID
INNER JOIN TRANS_PLAN TP ON TP.INV_DET_SEQ = ID.SEQ_NO
INNER JOIN PLAN_SEND_DET PSD ON PSD.SEQ_NO = TP.SEQ_NO
LEFT JOIN ( SELECT pbdX.*, pbmX.TRANS_DATE BID_DATE
FROM PLAN_BID_DET pbdX
INNER JOIN PLAN_BID_MAS pbmX on pbdX.PLAN_BID_SEQ = pbmX.SEQ_NO
INNER JOIN PLAN_SEND_DET psdX on pbdX.TRANS_PLAN_SEQ = psdX.SEQ_NO AND psdX.CANCEL_DATE IS NULL
WHERE pbmX.BIDDER_SEQ = ISNULL(23, pbmX.BIDDER_SEQ)
) pb ON pb.TRANS_PLAN_SEQ = psd.SEQ_NO
WHERE CAST(PSD.FINAL_DATE AS DATE) = CAST(GETDATE()-3 AS DATE) AND PSD.FINAL_TYPE= 1 AND PSD.CANCEL_DATE IS NULL
)X
GROUP BY SEQ_NO,VPACKET_NO,CTS
)BID
) MAIN

King Fisher 28-Dec-14 5:52am

Could you show your table Structure.with some Sample data

RKparmar 28-Dec-14 23:24pm

I AM USING MORE THEN ONE TABLE , AND I ALREADY MENTION WHAT I AM GETTING RESULT.

3 solutions

Solution 1

Learn from here.its very Simple ,try yourself

http://blog.sqlauthority.com/2008/06/07/sql-server-pivot-and-unpivot-table-examples/[^]

Posted 25-Dec-14 22:49pm

King Fisher

Solution 2

In addition Mr.King,also you can refer this link.
http://www.mssqltips.com/sqlservertip/3000/use-sql-servers-unpivot-operator-to-help-normalize-output/[^]
http://sqlhints.com/2014/03/10/pivot-and-unpivot-in-sql-server/[^]

Hope this may help you.

Posted 26-Dec-14 1:05am

Rajesh waran

Comments

King Fisher 28-Dec-14 5:50am

It doesn't matter how many links we refer.The matter is what we refer.So its enough. ;)

Rajesh waran 28-Dec-14 6:28am

Cool !!! I'm just adding some links with u I didn't comment ur solution... Cool

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

RKparmar · Accepted Answer · 2014-12-28T18:12:00

SQL

any buddy can use this query like result and output.....

SELECT Tital,
       [SEND],
       [BID],
       [FINAL]
FROM   (SELECT t.TITAL AS tt,
               c.TITAL,
               cdata
        FROM   Tablename t
               CROSS apply ( VALUES ('PCS',[PCS]),
                                    ('RGH_CTS',[RGH_CTS]),
                                    ('VALUE',VALUE) ) c (TITAL, cdata)) a
       PIVOT (Max(cdata)
             FOR tt IN([SEND],
                       [BID],
                       [FINAL])) piv