Select query by type wise

Question

Table1

Code, desc, type id

01    Rajan    1
01    Sajan    1
01    Vijayan  2
01    Suresh   3
01    Caresh   4
01    Sujesh   4
01    vikran   4
02    desk     1
02    card     2
02    villa    2
02    megash   2
02    supan    3
....

I want to view the table by type id wise

Expected Output

Code type-1 type-2 type-3 type-4

01   Rajan  Vijayan suresh caresh
01   Sajan  null    null   Sujan
01   null   null    null   vikran
02   desk   card    supan  null
02   null   villa   null   null
02   null   megash  null   null

How to make a query for the above condition

Need Query Help

Normally I'd say PIVOT here but I'm not sure you can do that in this case since you're dealing with varchars instead of numbers — Joe Phillips, Dec 25 '11 at 07:02

TetonSig · Accepted Answer · 2011-12-25T09:26:17.483

So first off just staging your data up. Note that I'm adding an identity row id for later.

IF OBJECT_ID('tempdb..#test') IS NOT NULL
   drop table #test
IF OBJECT_ID('tempdb..#Numbered') IS NOT NULL
   drop table #Numbered

 CREATE TABLE #test (Code CHAR(2), [DESC] varchar(10), [type id] INT, RowNumber INT IDENTITY(1,1))


INSERT #test 
    VALUES ('01', 'Rajan', 1),
           ('01' ,'Sajan', 1),
           ('01' ,'Vijayan', 2),
           ('01' ,'Suresh', 3),
           ('01' ,'Caresh', 4),
           ('01' ,'Sujesh', 4),
           ('01' ,'vikran', 4),
           ('02' ,'desk', 1),
           ('02' ,'card' ,2),
           ('02' ,'villa', 2),
           ('02', 'megash', 2),
           ('02', 'supan', 3)

Then we create a holding area that uses that row id to calculate which row of the code each of the names should go on.

  CREATE TABLE #Numbered
      (
       RowNum int, Code CHAR(2), [type] VARCHAR(10), [DESC] VARCHAR(10)
       )

   INSERT #Numbered
         SELECT (select count(*) from #test where code=t1.Code AND [type id]=t1.[type id] AND  RowNumber<=t1.RowNumber),
                 code, 
                [type id], 
                [DESC]
            FROM #test t1

Lastly, we create a PIVOT table on the data, (done in a standard SQL 2000 way of "faking" that operator). We then place that "PIVOT table" in a derived select that returns only the columns we want but allows us to sort on the code and rownum columns to generate the output you asked for.

     SELECT Code,[type-1],[type-2],[type-3],[type-4]
        FROM (Select P.Code,RowNum
                   , Min( Case When type = '1' Then [DESC] End ) As [type-1]
                   , Min( Case When type = '2' Then [DESC] End ) As [type-2]
                   , Min( Case When type = '3' Then [DESC] End ) As [type-3]
                   , Min( Case When type = '4' Then [DESC] End ) As [type-4]
                      From #Numbered As P
                        Group By P.Code,RowNum) R
        ORDER BY Code,RowNum

Please let me know if you want further explanation on any of this.

You query is working perfectly, i need to know how to use your query in view or store procedure, i am new to view & store procedure. Can you tell me please..... — Gopal, Dec 25 '11 at 10:20
Because there's some intermediate processing you can't use a view. You can wrap this entire code in a CREATE PROC GetSummaryData AS (all the code I listed) just make sure you change it to populate the first temp table with data from your actual table that contains the code,desc,type columns. insert #test select code,desc,type_id from yourtable — TetonSig, Dec 25 '11 at 14:49

score 0 · Answer 2 · answered Dec 25 '11 at 09:03

0

Select t1.code, t1.description, t2.description, t3.description from
(Select code, description from table where type=1) t1, 
(Select code, description from table where type=2) t2, 
(Select code, description from table where type=3) t3,
(Select code, description from table where type=4) t4
Where t1.code=t2.code and t2.code=t3.code and t3.code=t4.code and t4.code=t1.code
and t4.code=t2.code and t1.code=t3.code

Try this query it will not return null but will return repeated values

contact if you have any doubts

answered Dec 25 '11 at 09:03

Zlatan

698
7
16

thanks, i don't want to repeat the values, It should display null values instead of repeating values... – Gopal Dec 25 '11 at 09:43
Use case statement in the inner query ex case type=1 then description else NULL end – Zlatan Dec 25 '11 at 10:18

Select query by type wise

2 Answers2

Linked