Create row as a column in stored procedure

Question

I have the following three tables and I want to so as last table using a stored procedure. Can anyone tell me how I can join them?

Disc:

DiscId  DiscName
1         a
2         b
3         c

DiscDetail:

DiscDetailId  DiscId  DiscDetailName  Percentage
1             1       p               5
2             1       q               10
3             2       r               12
4             2       s               11
5             2       t               13
6             3       u               19
7             3       v               20

Pur:

PurId  DiscId
1      1
2      2
3      1
4      1

I want to show data as follows:

PurId  p  q   r   s   t
1      5  10  
2             12  11  13
3      5  10
4      5  10

score 0 · Answer 1 · edited Dec 10 '20 at 13:59

0

SELECT PurId ,
CASE WHEN DD.DiscDetailName ='P' THEN DD.Percentage END 'P',
CASE WHEN DD.DiscDetailName ='q' THEN DD.Percentage END 'q',
CASE WHEN DD.DiscDetailName ='r' THEN DD.Percentage END 'r',
CASE WHEN DD.DiscDetailName ='s' THEN DD.Percentage END 's',
CASE WHEN DD.DiscDetailName ='t' THEN DD.Percentage END 't'
FROM
(
SELECT * FROM DISC_DETAIL D
INNER JOIN  CHILD_TABLE CT ON DD.DiscId =CT.DiscId
)DD

Try above query.

edited Dec 10 '20 at 13:59

halfer

19,824
17
99
186

answered Jul 08 '17 at 08:43

Sagar Gangwal

7,544
3
24
38

There is Dynamic data of DiscDetail I can not Use Case When for p,q,r... – user3405179 Jul 08 '17 at 08:50
@user3405179 You can try according to below https://stackoverflow.com/questions/17964078/mysql-query-to-dynamically-convert-rows-to-columns-on-the-basis-of-two-columns – Sagar Gangwal Jul 08 '17 at 08:53
This is also fix column I have dynamic columns. – user3405179 Jul 08 '17 at 09:01
Kindly check whole answer. First he made query for static column and then make for dynamic for same query. – Sagar Gangwal Jul 08 '17 at 09:50

score 0 · Answer 2 · answered Jul 08 '17 at 14:46

Use dynamic pivoting:

First declare all the columns :

DECLARE @Columns AS NVARCHAR(MAX),
        @query  AS NVARCHAR(MAX);

    SET @Columns = STUFF((SELECT distinct ',' + QUOTENAME(c.DiscDetailName  ) 
                FROM DiscDetail c
                FOR XML PATH(''), TYPE
                ).value('.', 'NVARCHAR(MAX)') 
            ,1,1,'')

Create dynamic pivot:

set @query = 'SELECT PurId, ' + @Columns + ' from 
                (
                    select Pur.PurId
                        , DiscDetail.Percentage
                        , DiscDetail.DiscDetailName
                    from Pur 
                    inner join Disc on Pur.DiscID = Disc.DiscId  
                    inner join DiscDetail  on DiscDetail.ID = Disc.DiscId  
               ) x
                pivot 
                (
                     max(Percentage)
                    for DiscDetailName in (' + @Columns + ')
                ) p '


    execute(@query)

Create row as a column in stored procedure

2 Answers2